OraFAQ Forum: SQL & PL/SQL » Find a column Name using data

Home » SQL & PL/SQL » SQL & PL/SQL » Find a column Name using data (Oracle)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Find a column Name using data [message #630871]

Mon, 05 January 2015 23:54

hrdeepakkumar
Messages: 3
Registered: July 2010
Location: Bangalore

Junior Member

Hi All,

I have some data and i need to find in database what are all the column-tables are having this data.

Please somebody help me on this.

Example:
Data is "Retro"

Table1
Column1
Retro
retro
retor1

Table2
Column2
retronica

Table3
Column1 column2
a b

when i search i should get a result like table1-column1, table2-column2.

regards,
deepak

Report message to a moderator

Re: Find a column Name using data [message #630872 is a reply to message #630871]

Mon, 05 January 2015 23:56

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Welcome to the forum!

Please read and follow the OraFAQ Forum Guide and How to use [code] tags, to enable us to help you.

hrdeepakkumar wrote on Tue, 06 January 2015 11:24

when i search i should get a result like table1-column1, table2-column2.

1. What have you tried so far?
2. Explain the rules to get the desired output.

"Retro", "retro", "reTRo", "Retroblablabla" are not the same.

[Updated on: Tue, 06 January 2015 00:04]

Report message to a moderator

Re: Find a column Name using data [message #630875 is a reply to message #630872]

Tue, 06 January 2015 00:48

hrdeepakkumar
Messages: 3
Registered: July 2010
Location: Bangalore

Junior Member

Hi Lalith,

Thanks for your reply.

Rule is: All the columns which are having the data "retro"(string/substring) irrespective of the case(upper/lower), should be retrieved.

In the above example
"Retro", "retro", "reTRo", "Retronica" all these are valid strings. So i need the column and table names which are having these data.

Report message to a moderator

Re: Find a column Name using data [message #630876 is a reply to message #630875]

Tue, 06 January 2015 00:53

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Ok. Now show the query you have managed to write so far. If not, following are the hints :

1. *_TAB_COLUMNS view.
2. Regular expression to search in all the columns for all the tables. REGEXP_LIKE.
3. Make that search case insensitive.
4. You might need to write a function.

Now you have a start, try to write a query and show what it returns.

Report message to a moderator

Re: Find a column Name using data [message #630883 is a reply to message #630871]

Tue, 06 January 2015 01:15

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Have a look at T. Kyte's find_string procedure.

Report message to a moderator

Re: Find a column Name using data [message #630903 is a reply to message #630883]

Tue, 06 January 2015 03:42

hrdeepakkumar
Messages: 3
Registered: July 2010
Location: Bangalore

Junior Member

I have written a following block and working for me...
Thanks Michel and Lalit... Smile

declare
v_1 varchar2(4000);
BEGIN
for I in
(select * from USER_TAB_COLUMNS where DATA_TYPE ='VARCHAR2')
LOOP
begin
execute immediate('SELECT '||I.COLUMN_NAME||' FROM '||I.TABLE_NAME||' where REGEXP_LIKE('||I.COLUMN_NAME||',''retro'',''i'') and rownum=1') into V_1;
if V_1 is not null
then
DBMS_OUTPUT.PUT_LINE(I.TABLE_NAME||'-'||I.COLUMN_NAME);
end if;
EXCEPTION
when NO_DATA_FOUND then
null;
end;
end LOOP;
END;

Report message to a moderator

Re: Find a column Name using data [message #630904 is a reply to message #630903]

Tue, 06 January 2015 04:11

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Please use code tags, else the formatting will be lost.

Anyway, thanks for the feedback and sharing your solution.

Another solution -

SQL> var val varchar2(10)
SQL> exec :val := 'SCOTT'

PL/SQL procedure successfully completed.

SQL> SELECT DISTINCT SUBSTR (:val, 1, 11) "Searchword",
  2    SUBSTR (table_name, 1, 14) "Table",
  3    SUBSTR (COLUMN_name, 1, 14) "column"
  4  FROM cols,
  5    TABLE (xmlsequence (dbms_xmlgen.getxmltype ('select '
  6    || column_name
  7    || ' from '
  8    || table_name
  9    || ' where upper('
 10    || column_name
 11    || ') like upper('''
 12    || :val
 13    || '%'')' ).extract ('ROWSET/ROW/*') ) ) t
 14  ORDER BY "Table"
 15  /

Searchword  Table          column
----------- -------------- --------------
SCOTT       EMP            ENAME

SQL>

Report message to a moderator

Previous Topic:	Sql Querry based on condition
Next Topic:	Check for missing records in 75 million records per day

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 02:09:41 CDT 2024