How do I divide 1 column's data into 2 columns?

[MadProfessor]

Ok first off, sorry if this sounds simple but I am not a SQL genius by any stretch of the imagination.

Here's my problem: I have a SQL database mailing list which all the peoples' full names are in one column. I need to split this into 2 fields: first_name and last_name. I think I need a SQL statement (or perhaps in conjunction with an ASP script or something) that searches from the right of the string until it finds a space, then removes everything to the right of that space, and puts it into the last_name field. I can just then rename the original name field as first_name (I don't care really if there's more than 1 first name in this field).

Can anyone point me in the right direction? Is there a SQL function that will do this? Or does it involve a bit more in depth coding?

 

[tlbroadbent]

Using a combination of string functions, Left, Right, Charindex and Reverse, I came up with this solution.

Select
left(fullname,len(fullname)-charindex(' ',reverse(fullname),)) As FirstName
right(fullname,charindex(' ',reverse(fullname),)-1) As LeftName,
From Table1

I haven't tested it but believe it should work. Terry
------------------------------------
Blessed is the man who, having nothing to say, abstains from giving us worthy evidence of the fact. -George Eliot

 

[MadProfessor]

Thanks very much for this - I'll try it out tomorrow (It's late here where I am) and will let you know.

 

[ktighe]

Sorry if this doesn't help --> I don't know much about sql, either. It looks like you could do it like this:

use the PATINDEX('%pattern%', expression) function to get the position of the blank space
ex: PATINDEX(' ', [name field])

use the LEFT(character_expression, integer_expression) function to pull off the first name
ex: LEFT([name field],5) --> pulls out the first 5 chars

you could nest the PATINDEX function inside the LEFT funcion call (does sql support nesting like this? if not, just assign the PATINDEX result to a variable, then use the variable for the LEFT call)
ex: LEFT([name field],PATINDEX(' ',[name field]))

Look here for syntax notes:

http://ns.ddart.pe.kr/mssql/sql70/fa-fz_23.htm

Hope this helps. I've done this kind of thing in Access and Crystal Reports, but I know very little about SQL, so be warned.

Kevin

 

[MadProfessor]

The above worked fine (seleting the first name/last name from the single). But my severe lack of SQL knowledge is causing me headaches again. My problem now is updating the 2 new columns with the multiple values. I want to put all divided names into 2 new columns (first and last name). How do I do this???? The update statements I have done get an error message about not being able to update the multiple values that I extracted with the select statement. Am I using the wrong operator (=)?

 

[tlbroadbent]

Update Table1
Set FirstName = left(fullname,len(fullname)-charindex(' ',reverse(fullname))),
LastName = right(fullname,charindex(' ',reverse(fullname))-1)

I also noted that I mistyped the Select statement. It should read.

Select
left(fullname,len(fullname)-charindex(' ',reverse(fullname))) As FirstName
right(fullname,charindex(' ',reverse(fullname))-1) As LastName,
From Table1
Terry
------------------------------------
Blessed is the man who, having nothing to say, abstains from giving us worthy evidence of the fact. -George Eliot

 

[MadProfessor]

I've sorted this all out. I was just being stupid (probably due to hangover). Thanks for everyone for their help.