Invalid length parameter passed to the LEFT or SUBSTRING function.

  • susheelbharadwaj

    Valued Member

    Points: 62

    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

     

     

  • jonathan.crawford

    SSCertifiable

    Points: 6575

    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: 186107

    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

    [font="Arial"]“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]


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

  • Mr. Brian Gale

    SSC-Insane

    Points: 22926

    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...

  • Jonathan AC Roberts

    SSCoach

    Points: 17298

    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.

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

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