How to find column name that contains specific string value using oracle

Question

How to find column name contains particular string value in my table sku_config using oracle. for example my string is TRP , I need to find the column name that is having value 'TRP' in mytable. here column name can be any column belongs to my table. Here is psudo code for my requirement. select column_name from sku_config where contains 'TRP'.

Answer 1

You can use xmlquery as follows:

SELECT column_name FROM
(select column_name,
  to_number(xmlquery('/ROWSET/ROW/C/text()'
    passing xmltype(dbms_xmlgen.getxml(
      'select count(1) as c '
      || 'from ' || table_name || ' WHERE ' || column_name || ' LIKE ''%TRP%'''))
  returning content)) as c
from all_tab_columns
where TABLE_NAME = 'SKU_CONFIG')
WHERE C > 0;

Example:

Current data of sample table:

SQL> SELECT * FROM ABC;

NAME            DE
--------------- --
TEJASH2         SO
TEJASH3         DO
ABC             SO
XXXXXXXXX       SO
A               A
B               B
TEJASH1         SO

7 rows selected.

Searching for TEJASH string

SQL> SELECT column_name FROM
  2  (select column_name,
  3    to_number(xmlquery('/ROWSET/ROW/C/text()'
  4      passing xmltype(dbms_xmlgen.getxml(
  5        'select count(1) as c '
  6        || 'from ' || table_name || ' WHERE ' || column_name || ' LIKE ''%TEJASH%'''))
  7    returning content)) as c
  8  from all_tab_columns
  9  where TABLE_NAME = 'ABC')
 10  WHERE C > 0;

COLUMN_NAME
-------------
NAME

Searching for SO string

SQL>
SQL>
SQL> SELECT column_name FROM
  2  (select column_name,
  3    to_number(xmlquery('/ROWSET/ROW/C/text()'
  4      passing xmltype(dbms_xmlgen.getxml(
  5        'select count(1) as c '
  6        || 'from ' || table_name || ' WHERE ' || column_name || ' LIKE ''%SO%'''))
  7    returning content)) as c
  8  from all_tab_columns
  9  where TABLE_NAME = 'ABC')
 10  WHERE C > 0;

COLUMN_NAME
------------
DEPT

SQL>

Answer 2

If you want to find the names of the columns in a table that look like something, then use user_tab_columns:

select column_name
from user_tab_columns
where table_name = 'sku_config' and
      column_name like '%TRP%';