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

user_2997518 0
Viewed 967

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

oracle sql
3 Answers

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>
user_115656290

you can use the following code to find a column with a specific string.

select COLUMN_NAME
from TABLE_NAME
where COLUMN_NAME="STRING"  
group by COLUMN_NAME
user_91530630

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%';
user_11440350
Related