SQL query to filter the records based on a value across columns-CodePudding

My Scenario is to filter out the records which are having the value "RED" in any of the column

For eg: table name : Colors

ID   Col1     Col2   Col3
1    BLUE     RED      YELLOW
2    RED      GREEN    PINK
3    YELLOW    BLACK    BLUE
4    WHITE     GREY      RED

I have to retrieve the records 1,2,4 because they have RED in at least one of its column. I tried below query for the 3-column table

Select * from Colors
where Col1= 'RED' or Col2= 'RED' or Col3 ='RED'

But what if i have 100 columns in the table Colors. Is there any other way to filter for this condition?

CodePudding user response：

If we assume that you want to search through all columns with type VARCHAR2 you can find all ids of rows that contain 'Red' in a 'table' named 'Colors' like this:

DECLARE
    type found_array_t is varray(10000) of varchar2(100);
    found_array  found_array_t;
-- Type the owner of the tables you are looking at
  v_owner VARCHAR2(255) :='owner';

-- Type the data type you are look at (in CAPITAL)
-- VARCHAR2, NUMBER, etc.
  v_data_type VARCHAR2(255) :='VARCHAR2';

-- Type the string you are looking at
  v_search_string VARCHAR2(4000) :='RED';

BEGIN
  FOR t IN (SELECT table_name, column_name FROM all_tab_cols where owner=v_owner and (data_type = v_data_type or column_name='ID') and table_name = 'Colors') 
  LOOP
    IF t.column_name != 'ID' THEN
        EXECUTE IMMEDIATE 
        'SELECT ID FROM '||t.table_name||' WHERE '||t.column_name||' = :1'
        BULK collect INTO found_array
        USING v_search_string;
        FOR j IN 1..found_array.count
        LOOP
        dbms_output.put_line( t.table_name ||' '||found_array(j) );
        END LOOP;
    END IF;
  END LOOP;
END;

CodePudding user response：

Okay, you want to query a table with 100 column. just try this:

CL SCR

SET SERVEROUTPUT ON

DECLARE
    where_str   VARCHAR2(3000);
    str         VARCHAR2(3000);
    col_list    VARCHAR2(3000);
BEGIN
    where_str := 'SELECT LISTAGG(column_name,'' = ''''RED'''' OR '') WITHIN GROUP (ORDER BY column_name) || '' = ''''RED'''' '' FROM all_tab_cols
                  WHERE OWNER=''VARSA'' AND table_name=''COLOR'' AND  data_type=''VARCHAR2''';
    EXECUTE IMMEDIATE where_str INTO col_list;
    str := 'SELECT * FROM COLOR WHERE ' || col_list;
    dbms_output.put_line(str);
END;

This block gives you a query like this:

SELECT * FROM COLOR WHERE COL1 = 'RED' OR COL2 = 'RED' OR COL3 = 'RED';

Then you can run the query and see the result.