Simple Question

[rjdpa]

I have three fields in SQL..

Full Name, Last Name, First Name

Full Name = Doe, John
Last Name = NULL
First Name = NULL

What SQL statement would be best to parse the name and have it so I can get:

Full Name = Doe, John
Last Name = Doe
First Name = John


Any advice would be appreciated.

Thanks!

[Hack]

And what will you be running this code from?

VB6? VB.NET? Query Analyser? Something else?

[rjdpa]

SQL preferably

[dee-u]

SELECT Full Name, Last Name, First Name FROM Tablename

[kevchadders]

(based on SQL Server T-SQL)

Quick test to split them up.

-- Here is a quick example you can run yourself in T-SQL.
DECLARE @FullName AS VARCHAR(20)
SET @FullName = 'John, Doe'

SELECT SUBSTRING(@FullName, 1, CHARINDEX(',', @FullName) - 1) AS 'First Name'
SELECT LTRIM(SUBSTRING(@FullName, CHARINDEX(',', @FullName)+1, LEN(@FullName))) AS 'Last Name'

And how you could code it in an update...

-- Used in a update
UPDATE (table)
SET first_name = SUBSTRING(FullName, 1, CHARINDEX(',', FullName) - 1),
SET surname = LTRIM(SUBSTRING(FullName, CHARINDEX(',', FullName)+1, LEN(FullName)))

[FunkyDexter]

If you have the luxury I'd recommend populating the Last Name and First Name columns correctly then ditching the full name altogether. Having both introduces redundancy into your database and it's easier to concatenate two strings (to get the full name) than it is to split a string.