• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 568
  • Last Modified:

Split string into 3 parts in Oracle

Hello experts!

I need to split one string into 3 parts i.e. firstname, surname and degree.

While I am able to split into first and surname, I cannot extract the degree if there's one mentioned.

The strings to be split apart come in the following way:

Dr. James Example

Could you please assist me in splitting this string apart into three parts?

A million thanks in advance! I am curious about your appraches!

My current code is attached below. p_username is a parameter passed to the procedure!

select secondname || '\, ' || firstname || ' (LWF)' into ad_name from ( 
select substr(replace(p_username,',',''), 1, (instr (replace(p_username,',',''), ' ') - 1)) as firstname, 
substr (replace(p_username,',',''), (instr (replace(p_username,',',''), ' ',1,1) + 1), length (replace(p_username,',',''))) as secondname
from dual);

Open in new window

  • 4
  • 3
1 Solution
regexp_substr(p_username,'[^ ]+',1,1),
regexp_substr(p_username,'[^ ]+',1,2),
regexp_substr(p_username,'[^ ]+',1,3) from dual
if this is going to be used in pl/sql  don't use   select from dual

simply do direct pl/sql assignment

v_degree := regexp_substr(p_username,'[^ ]+',1,1);
v_firstname := regexp_substr(p_username,'[^ ]+',1,2);
v_surname := regexp_substr(p_username,'[^ ]+',1,3);

also,  your original query looks like it's searching for commas, but your example input doesn't contain any
what's the intent there?
skahlert2010Author Commented:
Wow you're ingenious and very firm in regexp functions as I can tell by now.

Thanks a lot sdstuber! That was very quick!
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.

skahlert2010Author Commented:
Well, would it be possible to have a fixed assignment for degree, first and surname?

For example If the person doesn't have a degree then
"regexp_substr(:p_username,'[^ ]+',1,1)  title" holds the firstname while the alias firstname contains the lastname. Lastname in turn is null.

skahlert2010Author Commented:
Okay, this is my solution to the problem I asked above! I know it's not perfect but it works:
case when title not in ('Dr.', 'Prof.') then title else firstname end as firstname,
case when title not in ('Dr.', 'Prof.') then firstname else secondname end as secondname,
case when title in ('Dr.', 'Prof.') then title end as title
from (select
regexp_substr(:p_username,'[^ ]+',1,1) title,
regexp_substr(:p_username,'[^ ]+',1,2) firstname,
regexp_substr(:p_username,'[^ ]+',1,3) secondname from dual)

Open in new window

that should work,
I'd have done it something like this...
check to see if there were 3 names, if so use them all, if not, skip the title

SELECT NVL2(s3, s1, NULL) title, NVL2(s3, s2, s1) firstname, NVL(s3, s2) surname
  FROM (SELECT REGEXP_SUBSTR(:p_username, '[^ ]+', 1, 1) s1,
               REGEXP_SUBSTR(:p_username, '[^ ]+', 1, 2) s2,
               REGEXP_SUBSTR(:p_username, '[^ ]+', 1, 3) s3
             from dual)
skahlert2010Author Commented:
Thanks sdstuber!

Nice solution! I'll adopt it! Sometimes it's amazing how many ways lead to Rome!

Take care!

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now