How to query through all varchar columns for a specific char

Question

I have to search for the char ' - ' in all columns (and lines) of my table. Currently I have this:

SELECT * 
FROM `my_table` 
WHERE (
    SELECT C.COLUMN_NAME 
    FROM INFORMATION_SCHEMA.COLUMNS C 
    WHERE C.TABLE_NAME = 'my_table'
) LIKE('%-%');

But, of course, this doesn't work because of the multiple values returned by the subquery. Any ideas of how to achieve this?

how many columns do you have? – RoMEoMusTDiE Oct 23 '19 at 21:41 — RoMEoMusTDiE, Oct 23 '19 at 21:41

score 2 · Answer 1 · answered Oct 23 '19 at 21:54

You can get the query with a SELECT on INFORMATION_SCHEMA.COLUMNS and execute the query with a Prepared SQL Statement on MySQL:

SET @query = '';
SELECT CONCAT('SELECT * FROM table_name WHERE ',GROUP_CONCAT(C.COLUMN_NAME SEPARATOR ' LIKE ''%-%'' OR '), ' LIKE ''%-%''')  FROM INFORMATION_SCHEMA.COLUMNS C WHERE C.TABLE_NAME = 'table_name' INTO @query;

PREPARE stmtSearch FROM @query;
EXECUTE stmtSearch;
DEALLOCATE PREPARE stmtSearch;

demo on dbfiddle.uk

How to query through all varchar columns for a specific char

1 Answers1