Solved

oracle sql string split

Posted on 2014-12-02
5
718 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 73

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 73

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 73

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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Read about achieving the basic levels of HRIS security in the workplace.
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…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

786 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