April 18, 2017 at 8:28 am
Hi,
I'm using the below function in select query and getting error msg as "Invalid length parameter passed to the LEFT or SUBSTRING function".
How can I handle this ?
LEFT(U.FULLNAME,charindex(',', U.FULLNAME)- 1) AA, CHARINDEX(LEFT(U.FULLNAME,charindex(',', U.FULLNAME)- 1), P.Project_Name) CCOUNT
April 18, 2017 at 8:38 am
Most likely your charindex is returning a value of 0, and thus erroring. For example, if FULLNAME contained a value of 'Thom', charindex(',', U.FULLNAME) would return a value of 0. This means that your left function would attempt to return the -1 left characters. Of course, that isn't going to work.
So, for your first column, try:CASE CHARINDEX(',', U.FULLNAME) WHEN 0 THEN U.FULLNAME
ELSE LEFT(U.FULLNAME,CHARINDEX(',', U.FULLNAME)- 1) END AS AA
See if you can then apply this logic to your second column.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 18, 2017 at 8:38 am
To make that easier to read:LEFT(U.FULLNAME,charindex(',', U.FULLNAME)- 1) AA,
CHARINDEX(LEFT(U.FULLNAME,charindex(',', U.FULLNAME)- 1), P.Project_Name) CCOUNT
Next, as a thought, why not try replacing it with:charindex(',', U.FULLNAME)- 1 AA
to check what value you are getting for charindex. I imagine that you have some values of FULLNAME that have no comma in them so when it is looking for the length you are getting -1. You will likely need to add a filter in to either ignore those values or not use LEFT on them. One option would be to use a CASE statement like:CASE
WHEN (CHARINDEX(',',U.FULLNAME)-1 <= 0) THEN U.FULLNAME
ELSE
LEFT(U.FULLNAME, CHARINDEX(',',U.FULLNAME)-1)
END AS AA
Although I highly recommend you rename the alias "AA" to something more meaningful unless AA means something to you.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
April 18, 2017 at 8:40 am
April 18, 2017 at 8:41 am
I suspect that you have values of FULLNAME that don't contain a ",". In such cases, your CHARINDEX expressions will evaluate to -1, which is an invalid length parameter to pass to the LEFT function. I think you'll need a CASE expression to handle comma-free names.
John
April 18, 2017 at 9:54 am
Thanks guys for a prompt response. I manage to implement the solution for 1st column.
For second column,I have applied the condition like below. Although, no errors, I'm unsure if this is a right solution.
CASE CHARINDEX(',', U.FULLNAME)
WHEN 0 THEN CHARINDEX(U.FULLNAME, Project_Name)
ELSE CHARINDEX(LEFT(U.FULLNAME,CHARINDEX(',', U.FULLNAME)- 1), Project_Name) end as CCOUNT
April 18, 2017 at 10:05 am
techsugy2017 - Tuesday, April 18, 2017 9:54 AMThanks guys for a prompt response. I manage to implement the solution for 1st column.For second column,I have applied the condition like below. Although, no errors, I'm unsure if this is a right solution.
CASE CHARINDEX(',', U.FULLNAME)
WHEN 0 THEN CHARINDEX(U.FULLNAME, P.PRJ_NAME)
ELSE CHARINDEX(LEFT(U.FULLNAME,CHARINDEX(',', U.FULLNAME)- 1), Project_Name) end as CCOUNT
If you are unsure of the results, do you have somebody that can verify the data? Since you are selecting it, copy the data, dump it into excel and have an end user verify it. Could even add an extra column on with something like:CASE CHARINDEX(',', U.FULLNAME)
WHEN 0 THEN 'N'
ELSE 'Y' as CommaInFullName
That way you can see which columns have no comma in the FULLNAME and can see where the case statements are being applied quickly and easily.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
April 18, 2017 at 10:13 am
It seems like everyone is checking for the existence of the comma to get the portion of the string. I'd rather make sure that there's at least a comma in the string. It's simpler.
SELECT LEFT(U.FULLNAME,charindex(',', U.FULLNAME + ',')- 1) AA
FROM (VALUES('Cazares, Luis'), ('Luis Cazares'))U(Fullname)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply