Invalid length parameter passed to the LEFT or SUBSTRING function.

Question

Home
Forums
SQL Server 2016
SQL Server 2016 - Development and T-SQL
Invalid length parameter passed to the LEFT or SUBSTRING function.

Post reply

Invalid length parameter passed to the LEFT or SUBSTRING function.

WallyWallWal
Old Hand

Points: 330
More actions
September 9, 2021 at 4:20 pm
Go to Answer
#3928281
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 #'
- This topic was modified 2 years, 11 months ago by WallyWallWal .
ScottPletcher
SSC Guru

Points: 101034
More actions
September 9, 2021 at 6:00 pm
Answer
#3928320

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 13, 2021 at 9:53 am

This was removed by the editor as SPAM

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Answer 1

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.

Answer 2

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!

Answer 3

WallyWallWal

Old Hand

Points: 330

September 9, 2021 at 6:23 pm

#3928324

That worked for me! Thanks Scott!

Answer 4

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