CODEWITHSUNDEEP
sqloracle-database
user2997518
user2997518

Reputation: 852

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

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

Upvotes: 4

Views: 3076

Answers (3)

Mujahidul Islam
Mujahidul Islam

Reputation: 563

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271151

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

Upvotes: 1

Popeye
Popeye

Reputation: 35930

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>

Upvotes: 1

Related Questions