11

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 = ?
| improve this answer | |
  • 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
15

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.

| improve this answer | |
4

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   
| improve this answer | |
2

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.

| improve this answer | |
  • I hope you are mocking me, as the alternative is that you thought this would be helpful. – donutguy640 Sep 19 '19 at 17:05
  • 2
    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
  • 1
    @donutguy640 and yet you selected your own answer using an external tool as helpful? seems like bologna. this is a better answer than using mysql workbench. geeze man have some respect at least use heidi – Garet Claborn Jun 12 at 19:43
  • @GaretClaborn I liked the result much better than searching through a raw text file. Why does this need explaining? So what if it's an "external tool"? Tools are useful, and the answer I stumbled on was interesting. – donutguy640 Jun 14 at 3:16
  • 1
    @donutguy640 no problem :) I agree that using mysqldump and grep is far from user-friendly and looks very basic in hindsight, but many people will not even think of it when they are fixed on finding an SQL solution. And with the question and answers being public now anyone else may come here and see it, and for some of them the CLI solution may be much more accessible than installing and connectig Workbench. – jkavalik Jun 14 at 20:43
1

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”.

| improve this answer | |
  • 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
1

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

| improve this answer | |
  • it is really not right that you have selected your own answer as correct when it doesn't actually answer your question – Garet Claborn Jun 12 at 19:39
  • O_o it's interesting to learn I don't get to decide what answers my question...oooh, wait, I DO get to! See, being the one who posted the question, I have this little green check mark I can click to mark an answer as the correct one! And, when I stumbled on this context menu option, and saw that it did EXACTLY what I was trying to do when I posted the question, I thought that information might be useful to others, and posted it here and marked it as the right answer! Amazing, isn't it? – donutguy640 Jun 14 at 3:09
  • I just think it's fair to tell you why I decided to downvote. You're free to abuse and exploit the system sure. – Garet Claborn Jun 15 at 23:28
  • Just as you're free to be arrogant in telling me it didn't answer the question. Expect backlash. – donutguy640 Jun 16 at 1:46
  • it didnt answer the question which is how to do this in sql without a tool. if you want a tool there was already a better answer – Garet Claborn Jun 16 at 19:19
-1

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.

| improve this answer | |
-2

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.

| improve this answer | |
  • 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
  • 2
    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
  • 4
    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.