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.
| Author |
Topic |
|
dim
Yak Posting Veteran
57 Posts |
 Posted - 2011-07-28 : 09:07:42
|
| Hi I have a field in the table -Name ...which has values like these bowenjuly/Marie Anneedwards/MarkVotrano/Mark the format is like LastName/First Name Middle Name I need to split this Name field into first_name, middle_name (if any) and last_name columns in the same table....Please advice is there a way to achieve this...Dp |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2011-07-28 : 12:10:58
|
| This works, but only for the sample data you provided. It will break as soon as someone has more than one middle name, or only one name like 'Cher' or 'Maddona'. 'Lady Gaga' won't parse either. There is no way good to do what you're asking.JimDECLARE @Table Table (FullName varchar(30))INSERT INTO @Table select 'bowenjuly/Marie Anne' UNION ALLselect 'edwards/Mark' UNION ALLselect 'Votrano/Mark' SELECT substring(fullname,1,charindex('/',fullname)-1) as LastName , CASE WHEN charindex(' ',FullName) > 0 THEN substring(fullname,charindex('/',fullname)+1,len(fullname)-charindex(' ',fullname)+1) ELSE substring(Fullname,charindex('/',fullname)+1,100) END as FirstName , CASE WHEN charindex(' ',FullName) > 0 THEN substring(Fullname,charindex('/',fullname)+1,100) END as MiddleName FROM @tableJimEveryday I learn something that somebody else already knew |
 |
|
|
|
|
|
|
|
Parsing Name TSQL