Sql Select For All Records That May Holds Specific Value

January 24, 2024 Post a Comment

How to select all records,that may contain specific value that is known, without referring to specific column in SQL expression? For instance, i know,that some unknown column holds

Solution 1:

So, you want to do a Google-like free text search over your database. This can be done but the performance will be Teh Suck! Google is fast because it has indexes on its indexes, duplicate data stores and generally optimizes everything for precisely this kind of search.

Anyway, here is a proof of concept using dynamic SQL and the Oracle data dictionary. Note that I restrict the columns to the type of data I want to search for i.e. strings.

SQL>set serveroutput on size unlimited
SQL>declare2      dummy varchar2(1);
  3begin4for r in ( select table_name, column_name from user_tab_cols
  5where data_type in ('VARCHAR2', 'CHAR', 'CLOB') )
  6      loop
  7begin8execute immediate 'select null from '||r.table_name
  9||' where '||r.column_name||' like ''%&search_value%'' '10||' and rownum = 1'11into dummy;
 12              dbms_output.put_line('Found it in >>>'13||r.table_name||'.'||r.column_name);
 14          exception
 15when others then16-- bad practice ahoy!17null;
 18end;
 19end loop;
 20end;
 21/
Enter valuefor search_value: MAISIE
old9:                ||' where '||r.column_name||' like ''%&search_value%'' 'new9:                ||' where '||r.column_name||' like ''%MAISIE%'' '
Found it in>>>T23.NAME

PL/SQLprocedure successfully completed.

SQL>

A more robust implementation might need to handle case, whole words, etc. If you're on 10g or higher then regular expressions could be useful, but combining regex and dynamic SQL is an, er, interesting prospect.

I repeat that performance is going to be Teh Suck! on a large data set. It is virtually impossible to tune, because we cannot index every column, and certainly not to support LIKE or similar fuzzy matches. An alternative approach would be to use XQuery to generate an XML representation of your data and then use Text to index it. Maintaining such a repository would be overhead, but the effort would be a sound investment if you need this functionality of a regular basis, especially in a production environment.

We can conduct a broader search across all the tables we have privileges on by using all_tab_cols instead.

for r in (select owner, table_name, column_name from all_tab_cols
                   where data_type in ('VARCHAR2', 'CHAR', 'CLOB') )

Obviously we need to prefix the owning schema in the generated statement.

execute immediate 'select null from '||r.owner||'.'||r.table_name
                       ||' where '||r.column_name||' like ''%

Solution 2:

SELECT*FROMtableWHEREcolumn='xxx';

But if you have many columns which can contain this value, you need to use OR:

SELECT*FROMtableWHERE column1='xxx'or column2='xxx'or column3='xxx';

Solution 3:

If you cannot explicitly write all the possible columns, you should generate a dynamic SQL query using the schema metadata.

Solution 4:

If you need to do this once or twice then APC's answer is good. If this is somehow (shudder) part of an ongoing requirement, then I think the best you'll be able to do is to create an Oracle computed field on the table or tables of interest and search on that. Use a delimiter that you're sure won't show up in the actual text values, e.g.:

altertable mytable add search_column 
 as (mycolumn1||'^'||mycolumn2||'^'||mycolumn3);

Now your query becomes something like:

select <whatever transformation you want to see here> 
from mytable where search_column like'%^xxx^%'

(That sound you may have just heard was Codd spinning in his grave)

Solution 5:

select * from table_name where(Table_Attribute='XXX');

this will show you all records with attribute XXX

Lab Sql