HattrickNZ HattrickNZ - 29 days ago 4
MySQL Question

How do I do a REGEXP on all the columns in a table?

How do I do a REGEXP on all the columns?

This is my table:

mysql> select * from datatables_demo;
+----+------------+-----------+-------------+--------+------------+--------+
| id | first_name | last_name | position | office | start_date | salary |
+----+------------+-----------+-------------+--------+------------+--------+
| 1 | Tiger | Nixon | Accountant | Tokyo | 2016-11-08 | 320800 |
| 2 | Garrett | Winters | Accountant2 | Tokyo | 2016-11-08 | 170750 |
| 3 | Ashton | Cox | Accountant3 | Tokyo | 2016-11-08 | 86000 |
| 4 | Cedric | Kelly | Accountant4 | Tokyo | 2016-11-08 | 433060 |
+----+------------+-----------+-------------+--------+------------+--------+
4 rows in set (0.01 sec)


This is how I select all columns and do a REGEXP on 1 column
first_name
:

mysql> select * from datatables_demo WHERE first_name REGEXP 'T';
+----+------------+-----------+-------------+--------+------------+--------+
| id | first_name | last_name | position | office | start_date | salary |
+----+------------+-----------+-------------+--------+------------+--------+
| 1 | Tiger | Nixon | Accountant | Tokyo | 2016-11-08 | 320800 |
| 2 | Garrett | Winters | Accountant2 | Tokyo | 2016-11-08 | 170750 |
| 3 | Ashton | Cox | Accountant3 | Tokyo | 2016-11-08 | 86000 |
+----+------------+-----------+-------------+--------+------------+--------+
3 rows in set (0.00 sec)


How do I do a REGEXP on all the columns? This is my attempt but its not right.

mysql> select * from datatables_demo WHERE * REGEXP 'T';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '* REGEXP 'T'' at line 1
mysql>


could not work it out from the docs

Do I have to write out a REGEXP per column:

select * from datatables_demo WHERE column1 REGEXP 'T' OR column2 REGEXP 'T' OR columnN REGEXP 'T';

Answer

you can either write it per column or use concat prior to regexp it. please reveiew this one:

SELECT * FROM datatables_demo where CONCAT(first_name, last_name, position, office) REGEXP 'T';