We help IT Professionals succeed at work.

Hoist the colors! We’ve added location flags to usernames sitewide, so it's easier to connect with the global community on EE. View My Profile

x

query to parse string

gs79
gs79 asked
on
495 Views
Last Modified: 2014-01-10
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,
Comment
Watch Question

Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
what version of the db?  Full 4-digit version please  like 11.2.0.1   not 11g

Author

Commented:
Thanks

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
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)
Database Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Alex [***Alex140181***]Software Developer
CERTIFIED EXPERT

Commented:
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

Author

Commented:
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

Author

Commented:
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

Author

Commented:
As I have closed this..I have opened a new one..

https://www.experts-exchange.com/Database/Oracle/Q_28335896.html

Thanks

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.