10

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

23

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 Sep 16 '19 at 6:35
  • 10
    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. – Bill Karwin Sep 17 '19 at 2:05
14

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.

3

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 ('
             , GROUP_CONCAT(COLUMN_NAME)
             , ')')
from INFORMATION_SCHEMA.columns 
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   
0

Try this one:

select
    *
from
    _table_
where
    concat('.', col1, '.', col2, '.', col3, '.') like "%.search_value.%";

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

  • 1
    Could you explain why your solution works please? Code only answers aren't seen as a good answer here. – George.Palacios Sep 16 '19 at 8:21
  • it doesn't work. eg if te search contains % or _ or any column contains . – Jasen Sep 17 '19 at 7:12
  • 1
    @Jasen You can escape the special characters and use a different separator. – Anon0012398193 Sep 17 '19 at 7:26
  • @George.Palacios - what is it that you find unclear? – Anon0012398193 Sep 17 '19 at 7:26
  • picking the correct separator could be very difficult. this approach is very hard to do right – Jasen Sep 17 '19 at 7:31
0

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

-1

The simplest solution is doing

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

Unless the val is something that is a common occurence in sql syntax.

  • I hope you are mocking me, as the alternative is that you thought this would be helpful. – donutguy640 Sep 19 '19 at 17:05
  • I am definitely not. It is a valid way to find something if you do not know "where" it is in the db/table. The grep will return the 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 programatically, but it is simple and quite fast. – jkavalik Sep 19 '19 at 19:13
-2

Try This.

SELECT *
FROM table
WHERE CONCAT(col1,' ',col2,' ',col3)="val val val";

CONCAT() is used to separate column values same as here you can separate your column with space to specify the better result inside function and check the value also separated string with space and the result is in your hand.

-3

It's possible by using the information_schema database, we'll basically just list all the columns of a specific table then use GROUP_CONCAT to list them as one line then feed them in a IN statement.

select * from db.table where 'value' in (select GROUP_CONCAT(COLUMN_NAME) from INFORMATION_SCHEMA.columns where table_schema = 'db' and table_name = 'table');

This will only return a row if 'value' is present in all columns.

  • 1
    Do you mean if 'value' is present in any of the columns? In addition, you can add a predicate for string types like: ` and DATA_TYPE IN ('char','varchar')` – Lennart Sep 16 '19 at 10:15
  • 1
    This will do it, and accomplishes OP's goal of avoiding explicitly naming the columns. It's not a wildcard, but the GROUP_CONCAT against the information schema is as good as a wildcard of all column names. – tbrookside Sep 16 '19 at 17:14
  • 2
    I'm having trouble getting this to work. I replaced db and table with my schema and table names, is there anything else intended to be placeholders for actual names? Also, I tried the inner statement on its own, and am realizing that it CONCATs the column names, rather than a row's data. Is that what you had intended? – donutguy640 Sep 17 '19 at 0:53
  • 4
    With all due respect, this is wrong. Your query will compare 'value' to the names of columns, not their contents. I have to give this a downvote. – Bill Karwin Sep 17 '19 at 2:08
  • 3
    More accurately, this will compare the string 'value' to the string that is a comma-separated list of column names – essentially the same as where 'value' in ('col1,col2,col3…'). – Andriy M Sep 17 '19 at 9:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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