Invalid length parameter passed to the LEFT or SUBSTRING function.

Question

Post reply

Invalid length parameter passed to the LEFT or SUBSTRING function.

susheelbharadwaj

SSC Enthusiast

Points: 172

May 29, 2020 at 7:24 am

#3758375

HI,

I am using the below query to fetch a data from SQL server. But the query returned Invalid length parameter passed to the LEFT or SUBSTRING function. error.

Any help in solving the issue would be much appreciated. TIA.

SUBSTRING(output_response_xml, LEN(LEFT(output_response_xml, CHARINDEX ('>Thank you', output_response_xml))) + 1, LEN(output_response_xml) - LEN(LEFT(output_response_xml, 
    CHARINDEX ('>Thank you', output_response_xml))) - LEN(RIGHT(output_response_xml, LEN(output_response_xml) - CHARINDEX ('', output_response_xml))) - 1) as CRCDecision

Viewing 5 posts - 1 through 4 (of 4 total)

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

jonathan.crawford SSCertifiable Points: 6612 More actions · Answer 1

We can't see your data, but try the following:

1 - just return the charindex() and len() functions as separate fields, so you can see what the calculations are that are failing

2 - look in your data for records that do not contain the string '>Thank you' and exclude them in the WHERE clause if that is the reason your calculations fail

-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Make it simple - break up the expression using APPLY blocks:

SELECT *

FROM (SELECT output_response_xml = 'A few random words and then... >Thank you') d

CROSS APPLY (SELECT Startpos = LEN(LEFT(d.output_response_xml, CHARINDEX ('>Thank you', d.output_response_xml))) + 1) x1

CROSS APPLY (SELECT StringLength = LEN(d.output_response_xml) - x1.Startpos - LEN(RIGHT(d.output_response_xml, LEN(d.output_response_xml) - CHARINDEX ('', d.output_response_xml)))) x2

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 3

Quick check on your query, and if you look at the 3rd parameter to Substring (the LENGTH portion) you have this:

LEN(output_response_xml) - LEN(LEFT(output_response_xml, 
CHARINDEX ('>Thank you', output_response_xml))) - LEN(RIGHT(output_response_xml, LEN(output_response_xml) - CHARINDEX ('', output_response_xml))) - 1) as CRCDecision

Lets make some assumptions to make this easier. First, lets assume that output_response_xml ALWAYS contains the text '>Thank you'. Next, lets assume that text is never the FIRST or LAST bit of text in the string.

So for sake of testing, lets say that the output_response_xml value is "hello >Thank you < world". Not a real world thing, but we can use it for testing. This is 24 characters long.

So, using this value, lets break the rest of it up. LEN(output_response_xml) is 24. CHARINDEX('>Thank you', output_response_xml) is 7, CHARINDEX('',output_response_xml) is 0. So, lets do some substitiutions:

24 - LEN(LEFT(output_response_xml, 7)) - LEN(RIGHT(output_response_xml, 24 - 0)) - 1) as CRCDecision

Already this is looking more simple, and the problem is a bit more apparent... but lets keep going. LEN on a LEFT(*,7) will be 7, and doing a LEN(RIGHT(output_response_xml,24)) when the string length is 24 will give us 24, so lets do more substitutions:

24 - 7 - 24 - 1

OR -8. This is an invalid length value for a SUBSTRING. The problem is with that "CHARINDEX('',output_response_xml)" section as that is going to return 0 every time, so you are always going to have a negative value for your substring length.

From reading the SUBSTRING, I think you are wanting to take the string and read starting at "Thank you" until the end of the string, right? I just want to confirm that my interpretation of what this is SUPPOSED to do is correct.

Also, when I say "string", I am meaning VARCHAR or NVARCHAR as I am pretty sure (not positive) that CHARINDEX, LEN, LEFT, RIGHT and SUBSTRING don't work on XML data...

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.

Jonathan AC Roberts SSCoach Points: 18356 More actions · Answer 4

If output_response_xml =''

then the expression:

LEN(output_response_xml) - LEN(LEFT(output_response_xml,

CHARINDEX ('>Thank you', output_response_xml))) - LEN(RIGHT(output_response_xml, LEN(output_response_xml) - CHARINDEX ('', output_response_xml))) - 1

Is equal to -1, which will give you the error you have.