How to Find which column has value We want?

Question

How to find which Column in Table (SQL) has the Value We're looking for ? e.g. I need to find "CAR" in My Table "Automobile".. when I know column name I'll write :

select * from Automobile where AutomobileName='CAR'

but consider I don't know which Column contains the Value, then ?

That's a use case I have never seen. Consider redesigning your tables. — Thomas Tschernich, Sep 08 '14 at 06:48
Why would this need arise? There is nothing built in for such situation. You will need to filter on all columns. Which database is it? SQL, MySql, Oracle? — danish, Sep 08 '14 at 06:50
take a look at this [question](http://stackoverflow.com/questions/639531/mysql-search-in-all-fields-from-every-table-from-a-database) may be useful for you — Farshad, Sep 08 '14 at 06:52

Lalit Kumar B · Answer 1 · 2014-09-09T15:36:51.580

This is one of the most common questions in any Database forums. Let me show you a test case in Oracle database :

I want to search all the tables in a schema having value 'SCOTT' and I will print the TABLE_NAME and the COLUMN_NAME.

Version :

SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
PL/SQL Release 12.1.0.1.0 - Production
CORE    12.1.0.1.0      Production
TNS for 64-bit Windows: Version 12.1.0.1.0 - Production
NLSRTL Version 12.1.0.1.0 - Production

Test case :

SQL> set serveroutput on;

SQL> DECLARE
  2      lcount NUMBER;
  3      lquery VARCHAR2(200);
  4  BEGIN
  5      FOR data IN (SELECT *
  6                   FROM   user_tab_columns) LOOP
  7          lquery := 'select count(*) from '
  8                    ||data.table_name
  9                    ||' where '
 10                    ||data.column_name
 11                    ||' like ''%SCOTT%''';
 12
 13          EXECUTE IMMEDIATE lquery INTO lcount;
 14
 15          IF lcount > 0 THEN
 16            dbms_output.Put_line(data.column_name
 17                                 ||'-----'
 18                                 ||data.table_name);
 19          END IF;
 20      END LOOP;
 21  END;
 22  /
ENAME-----EMP

PL/SQL procedure successfully completed.

SQL>

So you have the output as COLUMN_NAME --> ENAME and TABLE_NAME --> EMP that has the value 'SCOTT'.

Clarification as requested by a member :

SQL> CREATE TABLE t AS
  2    SELECT 'SCOTT' new_ename
  3    FROM   dual;

Table created.

SQL>
SQL> DECLARE
  2      lcount NUMBER;
  3      lquery VARCHAR2(200);
  4  BEGIN
  5      FOR data IN (SELECT *
  6                   FROM   user_tab_columns) LOOP
  7          lquery := 'select count(*) from '
  8                    ||data.table_name
  9                    ||' where '
 10                    ||data.column_name
 11                    ||' like ''%SCOTT%''';
 12
 13          EXECUTE IMMEDIATE lquery INTO lcount;
 14
 15          IF lcount > 0 THEN
 16            dbms_output.Put_line(data.column_name
 17                                 ||'-----'
 18                                 ||data.table_name);
 19          END IF;
 20      END LOOP;
 21  END;
 22  /
NEW_ENAME-----T
ENAME-----EMP

PL/SQL procedure successfully completed.

SQL>

So, now you can see the output as required.

@SATHYA, Perhaps you are too quick to post a comment than understanding the solution provided. In the SCOTT schema, there is ONLY one table named EMP and just one column named ENAME which has 'SCOTT' as the data, therefore you see it in the output. If you still do not understand, run the test case yourself. Else, look at the edit above posted ONLY FOR YOU. — Lalit Kumar B, Sep 08 '14 at 09:35
This is really annoying that few folks have voted down without understanding at all. It is a bad forum etiquette. — Lalit Kumar B, Sep 08 '14 at 10:57
`This is one of the most common questions in any Database forums` is quite a bold claim. I'd argue that 9 times out of 10, a question like this is a result of a problem with the schema. Also, your solution involves building SQL dynamically which prevents query/plan caching [possibly moot when you're scanning the whole table anyway]. Lastly, if nobody can see how good your answer is, then you need to improve it and make the value more obvious. — Basic, Sep 08 '14 at 15:40
I am not advocating that (ab)using dynamic sql for such requirements is a good way. It's just that OP asked for a solution, to which there are already several discussion, just name a database forum, and you will find such questions. The disadvantages and violation of normalization has already been suggested to OP via comments. I just provided a solution which would suffice OP's requirement. Rest depends on OP whether to implement this, or first fix the bigger issue of redesigning the tables. Remember, not always everything is immediately possible in an existing production environment. — Lalit Kumar B, Sep 08 '14 at 15:54
I have edited my answer and removed the name from the update statement since I now feel it inapropriate. I believe any suggestions and comments are for the enhancement of an answer and should be more than welcome. Thanks folks. — Lalit Kumar B, Sep 09 '14 at 15:41

score 1 · Answer 2 · answered Sep 08 '14 at 06:54

If you don't know which column it contains, you have to test them all:

SELECT *
FROM Automobile
WHERE AutomobileName = 'CAR'
OR AutomobileMake = 'CAR'
OR AutomobileYear = 'CAR'
...

If you need to know which column it was found in, you can use a UNION like this:

SELECT 'Name' AS WhichColumn, *
FROM Automobile
WHERE AutomobileName = 'CAR'
UNION
SELECT 'Make' AS WhichColumn, *
FROM Automobile
WHERE AutomobileMake = 'CAR'
UNION
SELECT 'Year' AS WhichColumn, *
FROM Automobile
WHERE AutomobileYear = 'CAR'
...

score 0 · Answer 3 · answered Sep 08 '14 at 06:57

you need to use full text search. the syntax is depend on your db engine in mysql for example it

SELECT productName, productline FROM products WHERE MATCH(productline,productline) AGAINST('Classic')

see http://www.mysqltutorial.org/introduction-to-mysql-full-text-search.aspx for more details

How to Find which column has value We want?

3 Answers3