Last post Sep 12, 2011 05:24 PM by smirnov
Sep 12, 2011 01:11 PM|wangxg26|LINK
I have a table that contain first name and last name sperating with space. What I need is either split it as two column, so I can have last name column; or is there any way I can select last name from this column and generate a new column as last name?
Sep 12, 2011 01:17 PM|limno|LINK
Check your data in your column and show us some samples. If you have only one space in the middle of your column, it will be easy. But if you have spaces within your names, you may have issues.
Sep 12, 2011 01:21 PM|UstesG|LINK
Here is something quick I came up with that splits on the space.
declare @name varchar(100)
set @name = 'wang xg'
SELECT @name, substring(@name, 1,charindex(' ',@name)) ,substring(@name, charindex(' ',@name),len(@name)-charindex(' ',@name)+1)
Sep 12, 2011 01:25 PM|smirnov|LINK
To select as two columns
select left(name, CHARINDEX(' ', name)) as firstname,
substring(name, CHARINDEX(' ', name)+1, len(name)-(CHARINDEX(' ', name)-1)) as lastname
where "name" is your column name.
To insert data as a new column, add a column, name it as "lastname" and run
set lastname=substring(name, CHARINDEX(' ', name)+1, len(name)-(CHARINDEX(' ', name)-1))
Sep 12, 2011 01:26 PM|nilsan|LINK
In addition to what limno said, I suggest that you check out that all of your records are consistent in form.
i.e FirstName LastName.
Then it would be easy for you or you have to find out probable odd case so that those cases can be taken care off.
Also check out
Sep 12, 2011 01:47 PM|wangxg26|LINK
There just have one space between first name and last name. Thanks, something like below:
Sep 12, 2011 01:52 PM|wangxg26|LINK
There are some cases, no name so the field value as null. some cases, there are middle initial, but if I figured out wiht first name and last name ONLY. I can split middle initial and last name again.
Sep 12, 2011 05:24 PM|smirnov|LINK
In this case you can use REVERSE function, example
select left(name, len(name)-CHARINDEX(' ', REVERSE(name))) as firstname,
substring(name, len(name)-CHARINDEX(' ', REVERSE(name))+1, len(name)) as lastname