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'.
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>
Comments
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%';
Comments
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
1 Comment
Tyler2P
Please don't post only code as answer, but also provide an explanation what your code does and how it solves the problem of the question. Answers with an explanation are usually more helpful and of better quality, and are more likely to attract upvotes.