×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Oracle SQL Code to split out a Full Name

Oracle SQL Code to split out a Full Name

Oracle SQL Code to split out a Full Name

(OP)
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.

RE: Oracle SQL Code to split out a Full Name

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:

CODE

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).

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

RE: Oracle SQL Code to split out a Full Name

(OP)

Great. It works.

Thank you. Appreciate the info.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close