Solved

oracle sql string split

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Help writing a query 6 73
Unable to save view in SSMS 21 59
Shredding xml into an oracle 11g Database 2 29
Running Total in Access 4 17
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
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 how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

896 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now