Home » SQL & PL/SQL » SQL & PL/SQL » Split the name into seperate columns
Split the name into seperate columns [message #219889] Fri, 16 February 2007 10:06 Go to next message
kavi123
Messages: 13
Registered: January 2007
Junior Member
Hi,

How to write a SQL query to split the name(eg: JOHN P WOOD) into three seperate columns as JOHN
P
WOOD

Few names are without middle initial and few names are PETER T.HUNT

Thanku very much,
Have a Great Day,
Kavi
Re: Split the name into seperate columns [message #219899 is a reply to message #219889] Fri, 16 February 2007 10:26 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Do you want it as 3 seperate columns, or 3 rows?
The sample you have shown us show Rows, but you say you want columns.

Which is it?
Re: Split the name into seperate columns [message #219911 is a reply to message #219899] Fri, 16 February 2007 11:58 Go to previous messageGo to next message
kavi123
Messages: 13
Registered: January 2007
Junior Member
Hi,
Iam sorry I have to split the name into three different columns not rows.

eg: JOHN P WOOD as JOHN P WOOD.
PETER E.HUNT as PETER E HUNT.
ROBIN CREST as ROBIN CREST.

Thanku,
Have A great Day,
Kavitha.
Re: Split the name into seperate columns [message #219945 is a reply to message #219911] Fri, 16 February 2007 17:37 Go to previous messageGo to next message
jrich
Messages: 35
Registered: February 2006
Member
If you are running under 10g you can try something like this:

select  regexp_substr(name, '^[^ ]*') first,
        trim(replace(regexp_substr(name, ' .*[. ]'), '.')) middle,
        regexp_substr(name, '[^ .]*$') last
from names;


Note this assumes a person always has at least a first and last name separated by a space. If they have a one word name, then that will be returned as both the first and last name.

The first regular expression means match from the beginning of the line up to a space char.

The second matches everything between a space character and a period or space char. If there is no middle name, NULL will be returned. The TRIM and REPLACE functions get rid of leading and trailing spaces and the potential period char at the end.

The last matches all nonspace/nonperiod characters at the end of the name.

If you are not on 10g, I would export the table to a flat file and then use a utility to perform regular expression replacement.

JR

[Updated on: Fri, 16 February 2007 17:38]

Report message to a moderator

Re: Split the name into seperate columns [message #220128 is a reply to message #219889] Mon, 19 February 2007 00:48 Go to previous messageGo to next message
pravin3032
Messages: 51
Registered: November 2006
Location: eARTH
Member
hi

this query works on oracle 8i/9i.
hope it will solve your prob.

SQL> 
SQL> select name from test11 ;

NAME
------------------------------
JOHN P WOOD
PETER T.HUNT
PRAVIN WARGHADE

SQL> 
SQL> select name,
  2         substr(name, 1, instr(name, ' ', 1) - 1) fst,
  3         substr(name, -1, instr(name, ' ', 1, 2) - 1) mi,
  4         substr(name, (instr(name, ' ', -1) + 1)) lst
  5    from test11;

NAME                           FST                            MI LST
-----------------    ------------------ --
JOHN P WOOD                    JOHN                           D  WOOD
PETER T.HUNT                   PETER                             T.HUNT
PRAVIN WARGHADE                PRAVIN                            WARGHADE
SQL> 


note : "E.HUNT" --> replace "." with single space.
Re: Split the name into seperate columns [message #220644 is a reply to message #219911] Wed, 21 February 2007 08:33 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
An option on 10g that would accommodate single names would be:

SQL> select name
  2	, regexp_substr(name, '[^ ]+') first
  3	, translate(regexp_substr(name, ' .+[ .]'), '- .', '-') middle
  4	, translate(regexp_substr(name, '[ .]\w+$'), '- .', '-') last
  5  from test_name;

NAME		FIRST	  MIDDLE	LAST
--------------- --------- ------------- ----------
John P Wood	John	  P		Wood
Peter E.Hunt	Peter	  E		Hunt
Robin Crest	Robin			Crest
Jeff		Jeff
Re: Split the name into seperate columns [message #221092 is a reply to message #219889] Fri, 23 February 2007 15:49 Go to previous messageGo to next message
srinivnp
Messages: 136
Registered: January 2006
Location: stlouis MO USA
Senior Member

Quote:
select asd,regexp_substr(asd,'^\w*') f1 ,
regexp_substr(asd,' . ') f2 ,
regexp_substr(asd,' \w{2,}') f3 from
(
select 'John A Smith ' asd from dual
union
select 'Garrt' from dual
union
select 'Foo Gold ' from dual
)
SQL> /

ASD F1 F2 F3
------------- -------------- ------------- -------------
Foo Gold Foo Gold
Garrt Garrt
John A Smith John A Smith
Re: Split the name into seperate columns [message #221094 is a reply to message #221092] Fri, 23 February 2007 16:21 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
srinivnp wrote on Fri, 23 February 2007 16:49

select asd,regexp_substr(asd,'^\w*') f1 ,
regexp_substr(asd,' . ') f2 ,
regexp_substr(asd,' \w{2,}') f3 from
(
select 'John A Smith ' asd from dual
union
select 'Garrt' from dual
union
select 'Foo Gold ' from dual
)

srinivnp, unfortunately, this won't accommodate the OP's varying middle name formats:

SQL> select name, regexp_substr(name,'^\w*') f1 ,
  2  regexp_substr(name,' . ') f2 ,
  3  regexp_substr(name,' \w{2,}') f3 from test_name;

NAME                 F1         F2         F3
-------------------- ---------- ---------- ----------
David Paul Allen     David                  Paul
John P Wood          John        P          Wood
Peter T.Hunt         Peter
Robin Crest          Robin                  Crest
Jeff                 Jeff
Re: Split the name into seperate columns [message #221165 is a reply to message #219889] Sat, 24 February 2007 10:56 Go to previous messageGo to next message
srinivnp
Messages: 136
Registered: January 2006
Location: stlouis MO USA
Senior Member


Ouch!!


Let us try this.

Quote:

select asd,regexp_substr (asd,'^\w*') f1 ,
regexp_substr(regexp_replace(asd,'(\w+)( *)(.*)','\3'),'^\w*') f2,
regexp_replace(asd,'(\w+)( *)(\w*)( *)(\w*)','\5') x3
from
(
select translate(asd,'.',' m') asd
from
(
select 'John A Smith' asd from dual
union
select 'Garrt' from dual
union
select 'Foo Gold' from dual
union
select 'Peter T.Hunt' from dual
union
select 'David Paul Allen' from dual
)
)
SQL>
SQL> /

ASD F1 F2 X3
-------------------------------- ---------- ---------- ----------
David Paul Allen David Paul Allen
Foo Gold Foo Gold
Garrt Garrt
John A Smith John A Smith
Peter T Hunt Peter T Hunt

SQL>

Srini
Re: Split the name into seperate columns [message #221167 is a reply to message #219889] Sat, 24 February 2007 11:48 Go to previous messageGo to next message
srinivnp
Messages: 136
Registered: January 2006
Location: stlouis MO USA
Senior Member


For Foo Gold , the previous query returns wrong result.

So let me make a third attempt.


Quote:

with foo as (
select 'John A Smith' asd from dual
union
select 'Garrt' from dual
union
select 'Foo Gold' from dual
union
select 'Peter T.Hunt' from dual
union
select 'David Paul Allen' from dual
union
select 'Thomas Xyz Thomas' from dual
union
select 'Dummy Dummy' from dual
)
select asd,f1,replace(replace(asd,f1),f3) f2, f3
from
(
select asd,regexp_substr (asd,'^\w*') f1 ,
regexp_substr(asd,' \w*$') f3
from
(
select translate(asd,'.',' m') asd
from foo
)
)
SQL> /

ASD F1 F2 F3
----------------- ---------- ---------- ----------
David Paul Allen David Paul Allen
Dummy Dummy Dummy Dummy
Foo Gold Foo Gold
Garrt Garrt
John A Smith John A Smith
Peter T Hunt Peter T Hunt
Thomas Xyz Thomas Thomas Xyz Thomas

7 rows selected.


Srini

Re: Split the name into seperate columns [message #221175 is a reply to message #221167] Sat, 24 February 2007 13:22 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Could you please use code-tags for your code and results? This will use a non-proportional font, making it much more readable.
That is [code] and [/code] instead of [quote] and [/quote]

[Updated on: Sat, 24 February 2007 13:24]

Report message to a moderator

Re: Split the name into seperate columns [message #221179 is a reply to message #221167] Sat, 24 February 2007 14:06 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Nice approach Srini. May still need to add a LTRIM around F2 & F3 to remove the leading space.

Heck, I thought you'd devise some MODEL query to accomplish the task Smile .

[Updated on: Sat, 24 February 2007 18:06]

Report message to a moderator

Re: Split the name into seperate columns [message #221222 is a reply to message #219889] Sun, 25 February 2007 13:26 Go to previous message
srinivnp
Messages: 136
Registered: January 2006
Location: stlouis MO USA
Senior Member

Ebrian ,
Good point . We need the spaces trimmed out.

Frank ,
Was unaware of that difference. Will do next time.


Srini



Previous Topic: date conversion
Next Topic: please advice me how to apply decode for two or more column (pivot table)
Goto Forum:
  


Current Time: Wed Dec 07 10:34:32 CST 2016

Total time taken to generate the page: 0.11006 seconds