I keep getting the "Invalid length parameter passed to the LEFT or SUBSTRING function." error when running my code for CHARINDEX. I have found why it is erroring but am unsure on how to correct it. I have read that I can use a CASE statement, but I am unsure on how to insert that into the code shown below. The error occurs when I do not have a second asterisk (*) in my code to close out the SUBSTRING/CHARINDEX. Any help or suggestions would be appreciated. Thanks!
Example: "*1234567ABC*" will allow the function to complete. "*1234567ABC" errors out the function
SUBSTRING(UCL.PROCEDURE_COMMENT,
CHARINDEX('*', UCL.PROCEDURE_COMMENT) + LEN('*'),
CHARINDEX('*', UCL.PROCEDURE_COMMENT, CHARINDEX('*', UCL.PROCEDURE_COMMENT) + LEN('*'))
- LEN('*') - CHARINDEX('*',UCL.PROCEDURE_COMMENT)
) AS 'MANUFACTURER #'
September 9, 2021 at 5:43 pm
My approach would be to look at where the problem is and determine what you want to do to fix it.
What you indicated is that the string MUST end in a *, but doesn't always end in a *, correct? If so, how do you want to fix it? Do you want to append a * when it is missing OR should your "Manufacturer #" be NULL when there is no * OR should it provide 1234567ABC when the string is *1234567ABC?
How you want to handle it will change what I recommend.
On top of that, are your strings for manufacturer number ALWAYS *<manufacturer #>*? If so, there is a much easier to read method to get the results using LEFT and RIGHT rather than SUBSTRING and a bunch of CHARINDEX's.
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.
Add an asterisk to the end of the string; it won't hurt if it's not needed but is vital if it is needed.
SELECT
SUBSTRING(UCL.PROCEDURE_COMMENT,
CHARINDEX('*', UCL.PROCEDURE_COMMENT) + LEN('*'),
CHARINDEX('*', UCL.PROCEDURE_COMMENT + '*', --<<--
CHARINDEX('*', UCL.PROCEDURE_COMMENT) + LEN('*'))
- LEN('*') - CHARINDEX('*',UCL.PROCEDURE_COMMENT)
) AS 'MANUFACTURER #'
FROM (
SELECT '*1234567ABC' AS PROCEDURE_COMMENT UNION ALL
SELECT '*1234567ABC*'
) AS UCL
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 9, 2021 at 6:04 pm
Thanks for your reply, Brian.
Strings for the manufacturer number will ALWAYS be *<manufacturer #>*. Staff at my organization have to manually enter this information into the field I am pulling into the code. Human error can occur and if they leave out the trailing asterisk the code fails.
What I would like the code to do is if the trailing * is omitted to just populate "*<manufacturer #>" and any other characters that may follow after "*<manufacturer #>" ( ex."*<manufacturer #>ABCDEF123456...."). I hope that this makes sense. If there is an easier method to code this, please do show me! =) Thanks!
September 9, 2021 at 6:23 pm
That worked for me! Thanks Scott!
September 9, 2021 at 6:29 pm
You could try PATINDEX twice to trim off any non 0-9 or A-z character. It works with any number of *'s, leading/trailing spaces, etc
select calc.*, substring(ucl.PROCEDURE_COMMENT,
calc.px_start,
calc.ln-calc.px_rev-calc.px_start+2) [MANUFACTURER #]
from (values ('*1234567ABC*'),
('***1234567ABC**')) ucl(PROCEDURE_COMMENT)
cross apply (values (patindex('%[0-9A-z]%', ucl.PROCEDURE_COMMENT),
patindex('%[0-9A-z]%', reverse(ucl.PROCEDURE_COMMENT)),
len(ucl.PROCEDURE_COMMENT))) calc(px_start, px_rev, ln);
px_start px_rev ln MANUFACTURER #
2 2 12 1234567ABC
4 3 15 1234567ABC
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy