Oracle SQL Code to split out a Full Name

[sa0309]

We are on Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi.

I'm needing to convert some SQL Server 2000 sql code to Oracle sql.

*** SQL Server Code

, CASE WHEN charindex(' ', RTRIM(SRV_NM)) > 1
THEN substring(RTRIM(SRV_NM), 1, charindex(' ', RTRIM(SRV_NM))-1)
ELSE RTRIM(SRV_NM)
END -- FIRST_NAME

, CASE WHEN charindex(' ', REVERSE(RTRIM(SRV_NM))) > 1
THEN RIGHT(RTRIM(SRV_NM), charindex(' ', REVERSE(RTRIM(SRV_NM)))-1)
END -- LAST_NAME


, CASE WHEN LEN(REPLACE(RTRIM(SRV_NM), SPACE(1), SPACE(0) )) < LEN(RTRIM(SRV_NM)) -1
THEN substring(RTRIM(SRV_NM), charindex(' ', RTRIM(SRV_NM)) + 1, LEN(RTRIM(SRV_NM)) -
charindex(' ', RTRIM(SRV_NM)) - charindex(SPACE(1), REVERSE(RTRIM(SRV_NM))))
END -- MIDDLE Name


Column SRV_NAME can be in these formats:

SRV_NAME
--------
Ella K. Cameron
Kenneth Chad Lambert



Results:
FIRST_NAME LAST_NAME MIDDLE_NAME
---------- ---------- ------------
Ella Cameron K.
Kenneth Lambert Chad



Any assistance appreciated.

Thank you.

 

[SantaMufasa]

SA,

Neither your code (in SQL Server) nor my code, below, fully handles the case where a person has a compound first or middle name or a maiden name such as "Mary Ann Elizabeth Smith Nelson". My code excises the "real" middle name and maiden name.

As to your specific coding request, there are many solutions in Oracle's SQL. Here is one of the solutions:

select * from sa0309;

SRV_NAME
--------------------
Ella K. Cameron
Kenneth Chad Lambert
John Doe
Mary Ann Elizabeth Smith Nelson

4 rows selected.

select substr(srv_name,1,instr(srv_name,' ')-1) First_name
      ,substr(srv_name,instr(srv_name,' ',-1)+1) Last_name
      ,decode(instr(srv_name,' ',1)-instr(srv_name,' ',-1)
             ,0,null
             ,substr(srv_name
                    ,instr(srv_name,' ')+1
                    ,(instr(srv_name,' ',1,2)-1)-instr(srv_name,' '))
             ) Middle_name
  from sa0309;

FIRST_NAME  LAST_NAME   MIDDLE_NAME
----------- ----------- -----------
Ella        Cameron     K.
Kenneth     Lambert     Chad
John        Doe
Mary        Nelson      Ann

4 rows selected.

Oracle's CASE statement works just like SQL Server's, so, as an illustration alternative, I used Oracle's DECODE function in my code, above.

The above code relies upon Oracle's "substr", "instr", and "decode" functions. If you have questions about any of the functions, please look over the on-line syntax definitions for them. If questions persist, please post questions back here.

Let us know if this resolves your question(s).

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[sa0309]

Great. It works.

Thank you. Appreciate the info.