

- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Find rows with a match in a pipe delimited column with MySQL
To find a match, use regular expressions in MySQL. Let us first create a table −
mysql> create table DemoTable -> ( -> Value varchar(60) -> ); Query OK, 0 rows affected (0.48 sec)
Insert some records in the table using insert command −
mysql> insert into DemoTable values('8|56|78|45'); Query OK, 1 row affected (0.24 sec) mysql> insert into DemoTable values('9876'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable values('98|8'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable values('3|8|9'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values('97|94'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable values('103|104|110|8|97'); Query OK, 1 row affected (0.10 sec)
Display all records from the table using select statement −
mysql> select *from DemoTable ;
This will produce the following output −
+------------------+ | Value | +------------------+ | 8|56|78|45 | | 9876 | | 98|8 | | 3|8|9 | | 97|94 | | 103|104|110|8|97 | +------------------+ 6 rows in set (0.00 sec)
Here is the query to find rows with a match in a pipe-delimited column −
mysql> select *from DemoTable -> where Value regexp '\\|8\\|';
This will produce the following output −
+------------------+ | Value | +------------------+ | 3|8|9 | | 103|104|110|8|97 | +------------------+ 2 rows in set (0.00 sec)
- Related Questions & Answers
- MySQL select distinct rows into a comma delimited list column?
- Select rows containing a string in a specific column with MATCH and AGAINST in MySQL
- How to collapse rows into a comma-delimited list with a single MySQL Query?
- Find rows where column value ends with a specific substring in MySQL?
- How to use a pipe with Linux find command?
- Replacing numbers on a comma delimited result with MySQL?
- MySQL query to count rows with a specific column?
- Combining multiple rows into a comma delimited list in MySQL?
- Find records with a specific last digit in column with MySQL
- How to find repeated rows and display there count in a separate column with MySQL?
- Find records with double quotes in a MySQL column?
- Fetch specific rows from a MySQL table with duplicate column values (names)?
- Select specific rows in a range with MySQL?
- How to find tables with a specific column name in MySQL?
- How to match a column in a data frame with a column in another data frame in R?
Advertisements