One of the requirements in our project was to extract individual column names from a string containing comma seperated values i.e if the string has say:
v_column_string := ',country_code,country_name,country_descripton,'
I wanted to get each column name from this string. Here's how I wrote the query for that..
[CODE]
SELECT *
FROM(
SELECT
SUBSTR(v_column_string ,
INSTR (v_column_string , ',' , 1 , A.LVL)+ 1,
INSTR (v_column_string , ',' , 1 , A.LVL + 1 ) -
INSTR (v_column_string , ',' , 1, A.LVL)-1
) AS COLUMNS
FROM
( SELECT ROWNUM LVL FROM ALL_OBJECTS WHERE ROWNUM <= no_of_words)A
) B
WHERE B.COLUMNS IS NOT NULL
OUTPUT :
country_code
country_name
country_desc
where no_of_words is the number of words in the String(in this case it is 3). In case you don't know how many words might be there, you can go ahead and give the length of the string here(although this will be an overhead as many nulls will be returned , which is why i have a filter for not null).
Notice that I have given the string with a leading and ending comma i.e ',country_code,country_name,country_desc,'. This is basically to extract the first word and the last. If you don't want to give these two comma's you can tweak the query to handle it.
Hope you wil find this useful someday:)
Monday, September 3, 2007
Subscribe to:
Post Comments (Atom)
The above query is for Oracle I have a similar query for DB2:
ReplyDeleteWITH TEMP(COL1,COL2) AS
(
SELECT 1,',' FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT LOCATE(',',v_column_string,COL1)+1 ,SUBSTR(v_column_string,COL1,LOCATE(',',v_column_string,COL1)-COL1) FROM TEMP
WHERE COL1 <= LENGTH(v_column_string)
)
SELECT DISTINCT COL2 FROM TEMP WHERE COL2 <> ',';