Stripping Name String into 2 columns

[Rozza]

Hi,

I have a table full of customer names. The name is stored in a single column and is in the following format:

Paul Rowling
Susan Smith
etc..........

What I want to do is to strip out the first part of the name and store this in a Forename column, and then strip out the second part of the name and store this in a surname column.

I am new to sql and need some guidance.

Any info will be greatly appreciated.

Cheers

Paul

 

[JamesLean]

Assuming there is only one space in the name, this should work for you:

UPDATE table_name
SET forename = LEFT(wholename, CHARINDEX(' ', wholename) - 1),
  surname = RIGHT(wholename, LEN(wholename) - CHARINDEX(' ', wholename))

If you have more than one space in a name, this will still work but you'll just have a space included in the surname column. --James

 

[sguslan]

One way is to use substring and charindex like this:
NOTE: do test it first on a copy of the original table!

update customers set
forname=
substring(name,1,charindex(name,' ',1)-1),
surname =
substring(name,charindex(name,' ',1)+1,len(name)-
charindex(name,' ',1))

Note again i am assuming name has only 2 parts separated by a space !

________________________________________________________________________________
If you do not like change, get out of the IT business...

 

[Rozza]

Hi JamesLean,

I have looked at your query and have tried it out. When I run it I get the following error:

Server: Msg 536, Level 16, State 3, Line 1
Invalid length parameter passed to the substring function.
The statement has been terminated.

I don't understand what is causing this

Any Ideas?

Cheers

Paul

 

[JamesLean]

Are there any NULL values in your wholename column? If so, add a WHERE clause to the UPDATE statement:

WHERE wholename IS NOT NULL

--James

 

[Rozza]

Just one other point, I have just tested the surname part and that works ok. It appears to be the forename part that causes the error

Cheers

Paul

 

[JamesLean]

In that case, I would guess there are some names which do not contain a space? If so, add a WHERE clause like this:

WHERE CHARINDEX(' ', wholename) > 0

--James

 

[Rozza]

Thanks James,

Works a treat!!