String manipulation ?

[timc]

I was given a script that was supposed to take a name field that was separated by commas and normalize it into last, first and middle name. My data looks like below in one fieldname called longname

crab,mike,Allen
Lota Weilly,Eric,M

My script to do this looks like

update ailoca
set last_name = substring (longname, 1, patindex( '%,%' , longname) -1 ),
first_name = substring (longname, patindex( '%,%' , longname) + 1, patindex( '% %', longname)-patindex( '%,%' , longname)),
middle_name = substring (longname, patindex( '% %', longname) + 1, len(longname)-patindex( '%,%' , longname))

My problem is that some people actually have 2 last names, not hyphenated, but 2. Whenever I have 2 names 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.

It seems to be related to the first name, I can comment out that update and it works

Thanks
Thanks

[skhanal]

What is the expected output?. Is it

Last Name
mike
Lota Weilly

First Name
crab
Eric

Middle Name
Allen
M

[timc]

For the field longname with the values

crab,mike,Allen
Lota Weilly,Eric,M
I want
lastname FirstName Middlename
Crab Mike Allen
Lota Weilly Eric M

[skhanal]

Try this

update ailoca
set last_name = (@longname, 1, patindex( '%,%' , @longname) -1 ),
first_name = substring (substring (@longname, patindex( '%,%' , @longname) + 1, len(@longname) - patindex( '%,%' , @longname)), 1, patindex( '%,%' , substring (@longname, patindex( '%,%' , @longname) + 1, len(@longname) - patindex( '%,%' , @longname))) -1 ),
middle_name = substring (substring (@longname, patindex( '%,%' , @longname) + 1, len(@longname) - patindex( '%,%' , @longname)), patindex( '%,%' , substring (@longname, patindex( '%,%' , @longname) + 1, len(@longname) - patindex( '%,%' , @longname))) + 1, len(substring (@longname, patindex( '%,%' , @longname) + 1, len(@longname) - patindex( '%,%' , @longname))) - patindex( '%,%' , substring (@longname, patindex( '%,%' , @longname) + 1, len(@longname) - patindex( '%,%' , @longname))))

[timc]

I replaced the @longname with the longname (the actual field name) and I get Server: Msg 170, Level 15, State 1,

Line 2: Incorrect syntax near ','.

Doesn't like the first line

[skhanal]

yeah, I forgot the substring

update ailoca
set last_name = substring(longname, 1, patindex( '%,%' , longname) -1 ),
first_name = substring (substring (longname, patindex( '%,%' , longname) + 1, len(longname) - patindex( '%,%' , longname)), 1, patindex( '%,%' , substring (longname, patindex( '%,%' , longname) + 1, len(longname) - patindex( '%,%' , longname))) -1 ),
middle_name = substring (substring (longname, patindex( '%,%' , longname) + 1, len(longname) - patindex( '%,%' , longname)), patindex( '%,%' , substring (longname, patindex( '%,%' , longname) + 1, len(longname) - patindex( '%,%' , longname))) + 1, len(substring (longname, patindex( '%,%' , longname) + 1, len(longname) - patindex( '%,%' , longname))) - patindex( '%,%' , substring (longname, patindex( '%,%' , longname) + 1, len(longname) - patindex( '%,%' , longname))))

[timc]

Unfortunately, I get the same error I was getting before

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

for any record that has 2 last names

[skhanal]

Can you give example of records with two last names?. For the sample you provided before, it worked.

I used variables and print statements to test

declare @longname1 varchar(50)
declare @longname2 varchar(50)

set @longname1 = 'crab,mike,Allen'
set @longname2 = 'Lota Weilly,Eric,M'



print substring (@longname1, 1, patindex( '%,%' , @longname1) -1 )
print substring (substring (@longname1, patindex( '%,%' , @longname1) + 1, len(@longname1) - patindex( '%,%' , @longname1)), 1, patindex( '%,%' , substring (@longname1, patindex( '%,%' , @longname1) + 1, len(@longname1) - patindex( '%,%' , @longname1))) -1 )
print substring (substring (@longname1, patindex( '%,%' , @longname1) + 1, len(@longname1) - patindex( '%,%' , @longname1)), patindex( '%,%' , substring (@longname1, patindex( '%,%' , @longname1) + 1, len(@longname1) - patindex( '%,%' , @longname1))) + 1, len(substring (@longname1, patindex( '%,%' , @longname1) + 1, len(@longname1) - patindex( '%,%' , @longname1))) - patindex( '%,%' , substring (@longname1, patindex( '%,%' , @longname1) + 1, len(@longname1) - patindex( '%,%' , @longname1))))


print substring (@longname2, 1, patindex( '%,%' , @longname2) -1 )
print substring (substring (@longname2, patindex( '%,%' , @longname2) + 1, len(@longname2) - patindex( '%,%' , @longname2)), 1, patindex( '%,%' , substring (@longname2, patindex( '%,%' , @longname2) + 1, len(@longname2) - patindex( '%,%' , @longname2))) -1 )
print substring (substring (@longname2, patindex( '%,%' , @longname2) + 1, len(@longname2) - patindex( '%,%' , @longname2)), patindex( '%,%' , substring (@longname2, patindex( '%,%' , @longname2) + 1, len(@longname2) - patindex( '%,%' , @longname2))) + 1, len(substring (@longname2, patindex( '%,%' , @longname2) + 1, len(@longname2) - patindex( '%,%' , @longname2))) - patindex( '%,%' , substring (@longname2, patindex( '%,%' , @longname2) + 1, len(@longname2) - patindex( '%,%' , @longname2))))

[timc]

You are correct, with the print it works, however when I run an update it fails. If you make a quick table with
4 fields all varchar(50) and call the fields longname,lastname,firstname,middlename, and then put in 2 records, one

lincoln, abe l
cronin tim, tom s

it will fail on the update using your script. Perplexed why print works and update fails

update table1
set lastname = substring (longname, 1, patindex( '%,%' , longname) -1 ),
firstname = substring (substring (longname, patindex( '%,%' , longname) + 1, len(longname) - patindex( '%,%' , longname)), 1, patindex( '%,%' , substring (longname, patindex( '%,%' , longname) + 1, len(longname) - patindex( '%,%' , longname))) -1 )
,middlename = substring (substring (longname, patindex( '%,%' , longname) + 1, len(longname) - patindex( '%,%' , longname)), patindex( '%,%' , substring (longname, patindex( '%,%' , longname) + 1, len(longname) - patindex( '%,%' , longname))) + 1, len(substring (longname, patindex( '%,%' , longname) + 1, len(longname) - patindex( '%,%' , longname))) - patindex( '%,%' , substring (longname, patindex( '%,%' , longname) + 1, len(longname) - patindex( '%,%' , longname))))

[skhanal]

It fails because you only have 1 comma in the new sample. You had 2 commas before.

If you use

insert into table1 (longname)
values ('crab,mike,Allen')

insert into table1 (longname)
values ('Lota Weilly,Eric,M')

It works fine. So you have to find out how many commas your data has, if they vary then this solution does not work.

[timc]

sorry actually the longname has
one comma so it looks like
cronin tim,tom shawn

I actually found a semi solution that works, I strip out the last name and then put the first/middle in a field, and then strip it out again and put it in the first and middle. Would rather do in 1 pass, but needed a solution quick, thanks for the help

update table1
set lastname = substring (longname, 1, patindex( '%,%' , longname) -1 ),
firstmi = substring(longname,patindex('%,%',longname)+1,len( longname))
update table1 set
firstname=substring(firstmi,1,patindex('% %',firstmi)-1),
middlename=substring(firstmi,patindex('% %',firstmi)+1,1)

[skhanal]

update ailoca
set last_name = substring (longname, 1, patindex( '%,%' , longname) -1 ),
first_name = substring(substring (longname, patindex( '%,%' , longname) + 1, len(longname) - patindex( '%,%' , longname)),1,patindex('% %',substring (longname, patindex( '%,%' , longname) + 1, len(longname) - patindex( '%,%' , longname)))-1),
middle_name = substring(substring (longname, patindex( '%,%' , longname) + 1, len(longname) - patindex( '%,%' , longname)),patindex('% %',substring (longname, patindex( '%,%' , longname) + 1, len(longname) - patindex( '%,%' , longname)))+1,len(substring (longname, patindex( '%,%' , longname) + 1, len(longname) - patindex( '%,%' , longname)))-1)