gs79
asked on
query to parse string
I have a table with two columns..
I am just displaying one record for brevity
COL_STR KEY
COL_A, COL_B, COL_C, COL_D COL_A
I need a query to process the above two values and return the following
return_val
TGT.COL_B = SRC.COLB
TGT.COL_C = SRC.COL_C
TGT.COL_D = SRC.COL_D
i would like the value in the key column to be excluded (in the above example COL_A)
if key has following
COL_A, COL_B
the output should be:
TGT.COL_C = SRC.COL_C
TGT.COL_D = SRC.COL_D
Please let me know if this is possible..
Thanks,
I am just displaying one record for brevity
COL_STR KEY
COL_A, COL_B, COL_C, COL_D COL_A
I need a query to process the above two values and return the following
return_val
TGT.COL_B = SRC.COLB
TGT.COL_C = SRC.COL_C
TGT.COL_D = SRC.COL_D
i would like the value in the key column to be excluded (in the above example COL_A)
if key has following
COL_A, COL_B
the output should be:
TGT.COL_C = SRC.COL_C
TGT.COL_D = SRC.COL_D
Please let me know if this is possible..
Thanks,
what version of the db? Full 4-digit version please like 11.2.0.1 not 11g
ASKER
Thanks
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
This should work in 10.2.0.1 and above
I made the assumption that you would have 10 columns or less. If that's not true, simply bump up the LEVEL <= 10 to something large enough to accomodate your data
SELECT 'TGT.' || str || ' = ' || 'SRC.' || str
FROM (SELECT REGEXP_SUBSTR(
col_str,
'[^ ,]+',
1,
n
)
str
FROM yourtable,
( SELECT LEVEL n
FROM DUAL
CONNECT BY LEVEL <= 10)
WHERE REGEXP_SUBSTR(
col_str,
'[^ ,]+',
1,
n
)
IS NOT NULL
MINUS
SELECT REGEXP_SUBSTR(
key,
'[^ ,]+',
1,
n
)
FROM yourtable,
( SELECT LEVEL n
FROM DUAL
CONNECT BY LEVEL <= 10)
WHERE REGEXP_SUBSTR(
key,
'[^ ,]+',
1,
n
)
IS NOT NULL)
I made the assumption that you would have 10 columns or less. If that's not true, simply bump up the LEVEL <= 10 to something large enough to accomodate your data
SELECT 'TGT.' || str || ' = ' || 'SRC.' || str
FROM (SELECT REGEXP_SUBSTR(
col_str,
'[^ ,]+',
1,
n
)
str
FROM yourtable,
( SELECT LEVEL n
FROM DUAL
CONNECT BY LEVEL <= 10)
WHERE REGEXP_SUBSTR(
col_str,
'[^ ,]+',
1,
n
)
IS NOT NULL
MINUS
SELECT REGEXP_SUBSTR(
key,
'[^ ,]+',
1,
n
)
FROM yourtable,
( SELECT LEVEL n
FROM DUAL
CONNECT BY LEVEL <= 10)
WHERE REGEXP_SUBSTR(
key,
'[^ ,]+',
1,
n
)
IS NOT NULL)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You're always doing a great job here at EE, sdstuber. But I think it would be nice, if you might explain your (awesome) SQL statements a bit more to the asker (so they may learn better to use and adept).
Just a thought ;-) Keep going!
Kind regards,
Alex
Just a thought ;-) Keep going!
Kind regards,
Alex
ASKER
Thanks sdstuber..It works..but I need to do this procedurally in a dynamic sql and generate a merge statement..i will close this and open and another question with more details
Thanks
Thanks
ASKER
Sdstuber..Is there a way to get result it as one value rather than multiple records
I just want the result as a value not the records, the above example gives me 3 records instead of record with:
TGT.COL_B = SRC.COLB
TGT.COL_C = SRC.COL_C
TGT.COL_D = SRC.COL_D
Thanks
I just want the result as a value not the records, the above example gives me 3 records instead of record with:
TGT.COL_B = SRC.COLB
TGT.COL_C = SRC.COL_C
TGT.COL_D = SRC.COL_D
Thanks
ASKER
As I have closed this..I have opened a new one..
https://www.experts-exchange.com/questions/28335896/query-contd.html
Thanks
https://www.experts-exchange.com/questions/28335896/query-contd.html
Thanks