Last name, first name issue

Hello,
I have a newbie question?
I have several hundred thousand names that is like this: Smith, John James.
How can I get them too look like this:
John James Smith?
TIAme
Look at this example amd contac the names as you need
CREATE TABLE T1 (ID INT,FullName VARCHAR(50))
INSERT INTO T1 VALUES (1, 'Smith, John')
INSERT INTO T1 VALUES (1, 'Richards, Wendy, C')
INSERT INTO T1 VALUES (1, 'Jones, Mike, Carl')
SELECT
fullname,
LEFT( fullname, CHARINDEX(',', fullname) - 1) AS surname,
LTRIM(SUBSTRING( fullname,CHARINDEX(',', fullname) + 1,CHARINDEX(
',',
fullname + ',', CHARINDEX(',', fullname) + 1) -
CHARINDEX(',', fullname) - 1)) AS forename,
CASE
WHEN LEN(fullname) - LEN(REPLACE(fullName, ',', '')) > 1
THEN LTRIM(SUBSTRING (
fullname,
CHARINDEX(
',',
fullname + ',',
CHARINDEX(',', fullname) + 1) + 1,
LEN(fullname)))
ELSE NULL
END AS middlenames
FROM T1
"me" <fromtheweb@.aaronminoo.com> wrote in message
news:ex3PUEZRGHA.4608@.tk2msftngp13.phx.gbl...
> Hello,
> I have a newbie question?
> I have several hundred thousand names that is like this: Smith, John
> James. How can I get them too look like this:
> John James Smith?
>
> TIA
>|||Uri,
Thanks alot.
But none of my names have a comma in-between the 1st and middle name.
I have tried removing the comma references in the last part of the code and
it does not work.
TIA
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23YEaX6aRGHA.252@.TK2MSFTNGP10.phx.gbl...
> me
> Look at this example amd contac the names as you need
> CREATE TABLE T1 (ID INT,FullName VARCHAR(50))
> INSERT INTO T1 VALUES (1, 'Smith, John')
> INSERT INTO T1 VALUES (1, 'Richards, Wendy, C')
> INSERT INTO T1 VALUES (1, 'Jones, Mike, Carl')
> SELECT
> fullname,
> LEFT( fullname, CHARINDEX(',', fullname) - 1) AS surname,
> LTRIM(SUBSTRING( fullname,CHARINDEX(',', fullname) + 1,CHARINDEX(
> ',',
> fullname + ',', CHARINDEX(',', fullname) + 1) -
> CHARINDEX(',', fullname) - 1)) AS forename,
> CASE
> WHEN LEN(fullname) - LEN(REPLACE(fullName, ',', '')) > 1
> THEN LTRIM(SUBSTRING (
> fullname,
> CHARINDEX(
> ',',
> fullname + ',',
> CHARINDEX(',', fullname) + 1) + 1,
> LEN(fullname)))
> ELSE NULL
> END AS middlenames
> FROM T1
>
> "me" <fromtheweb@.aaronminoo.com> wrote in message
> news:ex3PUEZRGHA.4608@.tk2msftngp13.phx.gbl...
>|||The quick answer is a kludgey program with a lot of T-SQL programming.
The *right* answer is to look up Mellisa Data or some other company
that scrubs mailing list data and use their tools. It is harder than
it looks with real data.|||On Sat, 11 Mar 2006 20:54:33 -0800, me wrote:

>Hello,
>I have a newbie question?
>I have several hundred thousand names that is like this: Smith, John James.
>How can I get them too look like this:
>John James Smith?
CREATE TABLE T1 (ID INT,FullName VARCHAR(50))
INSERT INTO T1 VALUES (1, 'Smith, John James')
INSERT INTO T1 VALUES (2, 'Richards, Wendy C')
INSERT INTO T1 VALUES (3, 'Jones, Mike Carl')
SELECT FullName,
RIGHT(FullName, CHARINDEX(',', REVERSE(FullName)) - 2) + ' ' +
LEFT(FullName, CHARINDEX(',', FullName) - 1)
FROM T1
DROP TABLE T1
Hugo Kornelis, SQL Server MVP