Solved

oracle sql string split

Posted on 2014-12-02
5
741 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 500 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 500 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

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

734 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