how to parse a name

[ja]

Hello,

I have a table that has a name field with the following data

john doe
john doe smith
john d smith

I need to separate the first, middle and last names into separate
fields. ex. first varchar (20), middle varchar (20) and last varchar
(20).

I am testing the process that I have to follow by doing the following

create table names (
name varchar (40),
first varchar (20),
middle varchar (20),
last varchar (20)
)

insert into names (name)
values (john doe smith)

I get an error message when I run the following query:

update b
set first = substring(name, 1, (charindex('', name)-1))

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

Does anybody have any suggestions?

TIA

ja

[Erland Sommarskog]

ja ([e-mail]) writes:

I get an error message when I run the following query:

update b
set first = substring(name, 1, (charindex('', name)-1))

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

Does anybody have any suggestions?

Change '' to ' ' to actually search for a space.

However, I would do this in a client language that is more powerful to
string manipulation than T-SQL. For instance, you may need to handle
multiple spaces.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[John Bell]

Hi
It looks like there is no space in the string being tested for in the
charindex call try:

update b
set first = CASE WHEN charindex(SPACE(1), name) > 1
THEN substring(name, 1, charindex(SPACE(1), name)-1)
ELSE name
END,
last = CASE WHEN charindex(SPACE(1), REVERSE(name)) > 1
THEN RIGHT(name, charindex(SPACE(1), REVERSE(name))-1)
END as LastName,
Middle = CASE WHEN LEN(REPLACE(name, SPACE(1), SPACE(0) )) < LEN(name) -1
THEN substring(name, charindex(SPACE(1), name)+1, LEN(name) -
charindex(SPACE(1), name) - charindex(SPACE(1), REVERSE(name)))
END

John

"ja" <[e-mail]> wrote in message
news:3h********************************@4ax.com...

Hello,

I have a table that has a name field with the following data

john doe
john doe smith
john d smith

I need to separate the first, middle and last names into separate
fields. ex. first varchar (20), middle varchar (20) and last varchar
(20).

I am testing the process that I have to follow by doing the following

create table names (
name varchar (40),
first varchar (20),
middle varchar (20),
last varchar (20)
)

insert into names (name)
values (john doe smith)

I get an error message when I run the following query:

update b
set first = substring(name, 1, (charindex('', name)-1))

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

Does anybody have any suggestions?

TIA

ja