Split lastname to different columns

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Split lastname to different columns

Author

Topic

Riklinssen
Starting Member

20 Posts

Posted - 2008-11-27 : 11:33:58

In my name field it happens (a lot) that both the male lastname and the female lastname are filled inside the name field. I would like to split the male name from the female name. There are about 15 ways the male name is divided from the female name. So this is programmable I think. To make everything I bit more clear I give 3 examples.
Example 1
LastName old: Berg e/v Huis
Male lastname:Berg
Female lastname:Huis
Example 2
Lastname Old: Dijk w/v Van Molen
Male lastnameName:Dijk
Female lastname:Van Molen
Example 3
Lastname old:Berg en dal - Janssen
Male lastname:Berg en dal
Female lastname:Janssen

How can I update this in my table?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-27 : 11:44:20

[code]UPDATE Table
SET Malelastname=CASE
WHEN PATINDEX('%e/v%',LastNameold)> 0
THEN RTRIM(LEFT(LastNameold,PATINDEX('%e/v%',LastNameold)-1))
WHEN PATINDEX('%w/v%',LastNameold)> 0
THEN RTRIM(LEFT(LastNameold,PATINDEX('%w/v%',LastNameold)-1))
WHEN PATINDEX('%-%',LastNameold)> 0
THEN RTRIM(LEFT(LastNameold,PATINDEX('%-%',LastNameold)-1))
END,
Femalelastname =CASE
WHEN PATINDEX('%e/v%',LastNameold)> 0
THEN LTRIM(SUBSTRING(LastNameold,PATINDEX('%e/v%',LastNameold)+1,LEN(LastNameold)))
WHEN PATINDEX('%w/v%',LastNameold)> 0
THEN LTRIM(SUBSTRING(LastNameold,PATINDEX('%w/v%',LastNameold)+1,LEN(LastNameold)))
WHEN PATINDEX('%-%',LastNameold)> 0
THEN LTRIM(SUBSTRING(LastNameold,PATINDEX('%-%',LastNameold)+1,LEN(LastNameold)))
END[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources