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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-11-28 : 09:14:10
|
| Ken writes "I have been searching for the answer to this but haven't been able to do it yet so thought I would see if someone else has done it. I have a name field with variations ('last, first' and 'last first'. I also have a employee id field with variations ('038 XXXX' and '56'). My question is how can I take what is in the name field and split the first name and last name into lastname and firstname columns and put the employee id into a numeric column instead of text. Any help is appreciated." |
|
|
sica
Posting Yak Master
143 Posts |
Posted - 2001-11-28 : 12:10:56
|
| I'm not sure if I understand what you want but you could try this:DECLARE @name VARCHAR(20), @employeeid VARCHAR(10)SET @name = 'test, test1' --SET @name = 'test test1'SET @employeeid = '038 XXXX' --SET @employeeid = '56'SELECT CASE WHEN CHARINDEX(', ',@name)>0 THEN SUBSTRING(@name,1,CHARINDEX(', ',@name)-1) ELSE SUBSTRING(@name,1,CHARINDEX(' ',@name)-1) END AS Forname, CASE WHEN CHARINDEX(', ',@name)>0 THEN SUBSTRING(@name,CHARINDEX(', ',@name)+1,LEN(@name)) ELSE SUBSTRING(@name,CHARINDEX(' ',@name)+1,LEN(@name)) END AS Lastname, CONVERT(INT,SUBSTRING(@employeeid,1,3))Edited by - sica on 11/28/2001 12:15:06 |
 |
|
|
|
|
|
|
|
splitting a column into multiple columns