Home » SQL & PL/SQL » SQL & PL/SQL » FirstName LastName Swap (Oracle 10g)
FirstName LastName Swap [message #573099] Thu, 20 December 2012 14:53 Go to next message
tgreen1
Messages: 13
Registered: December 2012
Junior Member
I have a need to make name field stored as "FirstName MI LastName" become "LastName, FirstName". Any suggestions on how to do this in a select statement. Because of the application I will be running this in (DataStage), at this point in the flow, I am unable to call a stored procedure.

Any suggestions are appreciated.

Thanks.
Re: FirstName LastName Swap [message #573100 is a reply to message #573099] Thu, 20 December 2012 15:05 Go to previous messageGo to next message
Littlefoot
Messages: 19476
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Do ALL names have 3 parts (first, middle, last name)? Or are there any exceptions?

Here's one option:
SQL> with test as
  2    (select 'First Middle Last' col from dual)
  3  select regexp_substr(col, '\w+$') || ', '||
  4         regexp_substr(col, '^\w+') new_col
  5  from test;

NEW_COL
-----------
Last, First

SQL>

[Updated on: Thu, 20 December 2012 15:08]

Report message to a moderator

Re: FirstName LastName Swap [message #573101 is a reply to message #573100] Thu, 20 December 2012 15:09 Go to previous messageGo to next message
tgreen1
Messages: 13
Registered: December 2012
Junior Member
Thanks for your reply.

Not all names will have a middle initial.
Re: FirstName LastName Swap [message #573102 is a reply to message #573101] Thu, 20 December 2012 15:29 Go to previous messageGo to next message
Littlefoot
Messages: 19476
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
OK; my previous query works fine in that case too.
SQL> with test as
  2    (select 'First Last' col from dual)
  3  select regexp_substr(col, '\w+$') || ', '||
  4         regexp_substr(col, '^\w+') new_col
  5  from test;

NEW_COL
-----------
Last, First

SQL>
Re: FirstName LastName Swap [message #573104 is a reply to message #573102] Thu, 20 December 2012 15:52 Go to previous messageGo to next message
tgreen1
Messages: 13
Registered: December 2012
Junior Member
This works perfect! Thank you so much!!!
Re: FirstName LastName Swap [message #573135 is a reply to message #573102] Fri, 21 December 2012 10:21 Go to previous messageGo to next message
Solomon Yakobson
Messages: 1994
Registered: January 2010
Senior Member
Littlefoot wrote on Thu, 20 December 2012 16:29
OK; my previous query works fine in that case too.


Not really, and it is not because of middle initial:

with test as (
              select 'Patrick John O''Brien' col from dual union all
              select 'Marie Skłodowska-Curie' col from dual union all
              select 'Johannes Diderik van der Waals' col from dual
             )
select  regexp_substr(col, '\w+$') || ', '||
        regexp_substr(col, '^\w+') new_col
  from  test
/

NEW_COL
------------------
Brien, Patrick
Curie, Marie
Waals, Johannes

SQL> 


I don't know how to deal with multi word (but not hyphenated) last names (third case), but this will be closer:

with test as (
              select 'Patrick John O''Brien' col from dual union all
              select 'Marie Skłodowska-Curie' col from dual union all
              select 'Johannes Diderik van der Waals' col from dual
             )
select  regexp_substr(col, '\S+$') || ', '||
        regexp_substr(col, '^\S+') new_col
  from  test
/

NEW_COL
-------------------------
O'Brien, Patrick
Sklodowska-Curie, Marie
Waals, Johannes

SQL> 


SY.
SY.
Re: FirstName LastName Swap [message #573136 is a reply to message #573135] Fri, 21 December 2012 11:11 Go to previous message
tgreen1
Messages: 13
Registered: December 2012
Junior Member
Great, thank you for your reply.
Previous Topic: full outer join performance
Next Topic: Outer Joins & Not Null check
Goto Forum:
  


Current Time: Fri Aug 22 21:29:24 CDT 2014

Total time taken to generate the page: 0.10588 seconds