I am curious, is there a good way to search all columns for a given value? For my purposes, it doesn't need to be at all fast, it's just a 1-off kinda thing, and I don't really want to have to type out every field name. That's precisely what I'll be doing for now, but I think surely there's a better way.

I would like to turn this:

SELECT * FROM table WHERE col1 = 'val' OR col2 = 'val' OR col3 = 'val';

into this:

SELECT * FROM table WHERE * = 'val'

...or, even better (though I seriously doubt it...)

SELECT * FROM table WHERE * like '%val%'

I found this, which seems kinda-not-really close, but I'm not finding anything closer:

SELECT whatever WHERE col1,col2 IN ((val1, val2), (val1, val2), ...)

Difference being, that searches a selection of columns for the specified values, whereas I'm trying to search ALL columns for a single value.

It's not important though, like I said more than anything I'm just curious

  • I'm baffled how everyone is throwing their answers to collect upvotes. If you don't have the solution, you shouldn't be consider it as an answer. It will be just a comment!!!
    – Ash
    Commented Jan 15 at 14:43

7 Answers 7


SQL doesn't provide a good way of doing this because it's probably not a good table design to have N columns that might have values in the same domain. This is potentially a case of "repeating groups" like if you have columns phone1, phone2, phone3, ... phoneN.

If you have a number of columns that have the same logical domain of values, it could be a case where it's a multi-valued attribute (like the phones example). The standard way of storing multi-valued attributes is as multiple rows in another table, with a reference to the row they belong to in the primary table.

If you do that, then you only have to search for the specific value in one column of the dependent table.

SELECT t.* FROM mytable AS t
JOIN phones AS p ON t.primaryKey = p.refKey 
WHERE p.phone = ?
  • 1
    And if for whatever reason you can't change the table layout, you could write a view that represents the data in this format, then query the view.
    – CodeCaster
    Commented Sep 16, 2019 at 6:35
  • 16
    No, just change the table layout. That's it. I'm tired of the excuse "I can't change it." I don't accept that anymore. It's time to learn how to change things. Commented Sep 17, 2019 at 2:05
  • This answer should be downvoted to the Antipodes. It first makes the assumption that the OP's DB design is faulty and then proceeds to rant based on that assumption. A simple question merits a simple answer without too much noise, else silence. I came here because I need to find filenames ending in .jpeg and convert to .jpg. I have several columns in several tables which hold different filenames for different purposes. And No! I don't have columns like file1, file2. I have infile, outfile, uri. Should I surrender myself to the SQL Police Station?
    – bliako
    Commented Jul 20, 2023 at 10:59
  • This is not true. I have so many columns in so many tables that they might have missing values (e.g. Null) and I want to see if there are any Null values there. In realtime data collection this is a common issue.
    – Ash
    Commented Jan 15 at 14:41
  • @Ash, You can check all the columns you want, but you must name the columns explicitly. There is no wildcard syntax in SQL for making expressions to check all the columns. Commented Jan 15 at 15:07

The closer you can get is this, using IN:

SELECT * FROM table WHERE 'val' IN (col1, col2, ..., colN) ;

You still have to write all the columns you want to check.
And it's not any different than the OR expression you have, not in performance or otherwise. This is just a different, equivalent way to write the expression, with a bit fewer characters.


Feels like it's been a year since I asked this, but I just stumbled on what appears to be the exact thing I was looking for! It's not a SQL statement, like I was anticipating, but it DOES what I was wanting.

In MySQL Workbench, you can right click the table or the schema, and choose Search Table Data.schema context menu

  • 2
    +1. This is very useful. It allows searching within the text and non-text (that can be converted to text, such as dates) columns of an arbitrary selection of schemas/tables from the Database Navigator.
    – Alexei
    Commented Aug 29, 2022 at 9:56
  • Yep. This was the answer that worked for me quickest Commented Aug 22, 2023 at 17:02

You need to do it in two steps, first generate the sql like (assuming your table is named T in schema S:

select concat(' SELECT * FROM t WHERE ''a'' in ('
             , ')')
where table_schema = 's' 
  and table_name = 't'
  and DATA_TYPE IN ('char','varchar');

Now you can execute this string. Note that you have to quote the 'a' with extra '. If you put this in for example a procedure, you can prepare and execute the string that was generated.

I tested with:

create table t (a char(3), b varchar(5), c int);
insert into t(a,b) values ('a','b'),('b','c'),('c','c');    

The query generated where:

SELECT * FROM t WHERE 'a' in (a,b)

and executing that results in:

a   b   c
a   b   

The simplest solution is doing

mysqldump ... --skip-extended-insert db table | grep 'val'

Unless val is something that is a common occurrence in SQL syntax.

This is a valid way to find something if you do not know "where" it is in the database or table. The grep will return entire rows (including the table name) where the value is present. It is not SQL and is not very useful if you want to process the output programmatically, but it is simple and quite fast.

Using mysqldump and grep may be far from user-friendly, and it might look very basic, but many people will not even think of it when they are fixed on finding a SQL solution. For some people, the CLI solution may be more accessible than, say, installing and connecting Workbench.



    concat('.', col1, '.', col2, '.', col3, '.') like "%.search_value.%";

The way this tries to solve the problem is by creating a long string containing every value of a given row, separated by a dot character. then it performs a string search for the desired term between two dots.

Note that if any column may be NULL, you should use CONCAT_WS() instead.

This assumes that there is no '.' in your search string. If you cannot guarantee that, maybe you can use a different "separator character".

If you want to search for substrings in the individual collumns, you can move the % signs to be inside the '.' like so:

    concat('.', col1, '.', col2, '.', col3, '.') like ".%search_value%.";

You can do this fairly easily in MySQL, as long as your have the FULLTEXT index set up against your table. It allows for a heap of different search mechanisms to be used including natural language, and additional metadata to score the results.

Taken from the MySQL manual:

mysql> SELECT * FROM articles
        WHERE MATCH (title,body)
| id | title             | body                                     |
|  1 | MySQL Tutorial    | DBMS stands for DataBase ...             |
|  5 | MySQL vs. YourSQL | In the following database comparison ... |
2 rows in set (0.00 sec)

This only really works for character-based columns. If you are looking for a specific value in numerical or other special types, you'll need to use CONCAT or other mechanisms to achieve that.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.