Error with SQL code
-
I am trying to concatenate last and first name into a systemID but have run into a bit of snag. Here's the code snippet:
last_Name + left(first_name,len(last_name)-9)
I get an error saying "Invalid length parameter passed to the substring function."
What am I missing?
THanks,
Walter
-
could you please post the entire code - or at least the part that includes the substring function...also some sample values from last_Name & first_name would help...
**ASCII stupid question, get a stupid ANSI !!!** -
ABS(Len(something) - 9)
Just to make sure that this value can't be negative.
-
Off the top of my head (like the rest of my hair), it looks like you cannot count on 'len (last_name) - 9)' being greater than zero for every value of 'last_name'.
-
ABS worked! I guess you can't have a negative length.
Thanks to all,
Walter
-
Well not with strings
. -
I don't think that ABS is going to fix the design issue, only mask the error. What if:
last_Name = 'Do' and first_name = 'Robert Henry
last_Name + left(first_name,ABS(len(last_name)-9))
Would result in 'DoRobert ' because 2 - 9 = -7 and ABS makes it 7
I think you should use:
LEFT(NULLIF(ISNULL(last_name,'')+ISNULL(first_name,''),''),9)
I cannot guess the reasoning behind your original expression.
Andy
-
I needed 9 characters. I have a case statement checking the length of the last name. If it is 9, use it. If it is more than 9 give me 8 and the first letter of the first name. If it is less than 9 I want all of the last and however many I need from the first Name to make 9 chars.
As for your concern about the space, I did run into that issue with O'Brien, where the apostrophe was counted, but it was only 2 of them, and I manually fixed those. Since this was a one-time run, ABS worked, but thanks for the alternate version. It might be useful in the future, or may help someone else.
-Walter
-
Well, now that you explained it, I'd suggest to abandon the special formula for last names with exactly 9 characters and make it always first 8 of last name plus whatever is needed from first name to make 9. BTW, what if the name is John Doe, which only makes 7? Supposing you would decide to supplement as many '#' as necessary in case the name is too short, you could write it like this:
SET systemID = LEFT(LEFT(last_name,8)+first_name+'#########',9)
In fact you simply concatenate everything and then take only as many character as required - this is very handy when you need to pad the result with something (like leading zeros in a numeric string - those of course using RIGHT instead of LEFT). It will work with any name, the only exception being that one of the columns is NULL - but of course that is easy to remedy using ISNULL, I just wrote the simplest form. I know you already did your work, but maybe you'll have a chance to use it later for something similar
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply