I would like to split a Name column which holds the full name with spaces
into 3 seperate columns. ie Title, Firstname, Surname.

Regards

Mike

This can be trickier than you think.   ""Herr Doktor Johan van der
Poon" is a nightmare for simple SQL statements.

But for a quickie, google up some old posts on comma separated list and
modify the code.

First, look up the string functions like CHARINDEX and SUBSTRING in SQL
Server Books Online. If you have difficulty in creating an expression to
split the value, then post back.

I hate this solution.
I would suggest you keep it as three seperate columns.
But there might be constraints on design change.. I understand :)
So here is the solution anyways.. I guess you can change the variable to the
column name

declare @name varchar(20)
set @name = 'asc dsds fdfdd'

select left(@name, charindex(' ',@name)) as firstname,
reverse(left(reverse(@name),charindex(' ',reverse(@name)))) as lastname,
left(left(@name, charindex(' ',@name)),charindex(' ',left(@name, charindex('
',@name)))) as middlename

Hope this helps.

change.. sorry

declare @name varchar(20)
set @name = 'asc dsds fdfdd'

select left(@name, charindex(' ',@name)) as firstname,
reverse(left(reverse(@name),charindex(' ',reverse(@name)))) as lastname,
substring(@name,charindex(' ',@name),charindex(' ',@name,charindex('
',@name) + 1) - charindex(' ',@name)) as middlename

/*untested*/
SELECT RTRIM(LEFT(Name, CHARINDEX(' ', Name))),
SUBSTRING(Name, CHARINDEX(' ', Name) + 1, LEN(Name) - CHARINDEX(' ',
REVERSE(Name)) - 1),
RIGHT(Name, CHARINDEX(' ', REVERSE(Name)) - 1)
FROM [Your Table]

And what CELKO said, this all falls apart when name with more that two
spaces occurs.


Show quoteHide quote

Different between ##table and #table regarding performance
Problems while migrating from MSSQL 2000 to 2005 - select... into fails in a trigger - please help.
sp question re: dynamic sql
Crazy Row Numbering Poblem
Using a CASE to Determine if a Column is included in the SELECT clause