Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

oracle sql string split

Posted on 2014-12-02
5
Medium Priority
?
780 Views
Last Modified: 2014-12-10
Hi - I have a string like "LNAME, FNAME MNAME SURNAME"
I am trying to write query in oracle to get just the FNAME and LNAME excluding others.

1) data is comma seperated
2) the data before comma is LNAME and first word after comma is FNAME.

How can I get that.

Thanks
0
Comment
Question by:shragi
  • 3
5 Comments
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 2000 total points
ID: 40476610
your sample data is not completely comma separated, but this should handle it regardless of using commas, spaces or both

select regexp_substr(yourstring,'[^, ]+',1,1) lname,
       regexp_substr(yourstring,'[^, ]+',1,2) fname
from yourtable
0
 

Author Comment

by:shragi
ID: 40476615
Thanks sdstuber - is there any other way with out regular expression
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 40476654
assuming the data is formatted as you have shown then try this...


SELECT SUBSTR(yourstring, 1, INSTR(yourstring, ',') - 1) lname,
       SUBSTR(
           yourstring,
           INSTR(yourstring, ', ') + 2,
             INSTR(
                 yourstring,
                 ' ',
                 1,
                 2
             )
           - INSTR(yourstring, ', ')
           - 2
       )
           fname
  FROM yourtable
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40476661
if the data is formatted as you described  - that is, actually comma separated, not commas and spaces

so something like "LNAME,FNAME,MNAME,SURNAME"

then try this...


SELECT SUBSTR(yourstring, 1, INSTR(yourstring, ',') - 1) lname,
       SUBSTR(
           yourstring,
           INSTR(yourstring, ',') + 1,
             INSTR(
                 yourstring,
                 ',',
                 1,
                 2
             )
           - INSTR(yourstring, ',')
           - 1
       )
           fname
  FROM yourtable
0
 
LVL 32

Expert Comment

by:awking00
ID: 40476701
The question that needs to be answered is, "Are there commas and/or spaces between each word and are there possible combinations of one or the other or both?
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to recover a database from a user managed backup

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question