Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

oracle sql string split

Posted on 2014-12-02
5
Medium Priority
?
791 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
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 copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

564 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