-
String manipulation ?
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
-
What is the expected output?. Is it
Last Name
mike
Lota Weilly
First Name
crab
Eric
Middle Name
Allen
M
-
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
-
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))))
-
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
-
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))))
-
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
-
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))))
-
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))))
-
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.
-
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)
-
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)
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|