invalid length parameter passed to the left or substring function - Error is not happening consistently for the same data

Question

I got the error

Invalid length parameter passed to the left or substring function

I understand that negative value is passed and this is reason for the error.

Substring query is to fetch the data between first dash and second dash

Ex: sample data "ABC-123-ABCDEF", expected result "123"

The error is not occurring consistently for the same data, error occur at times and working fine most of the times. I want to understand why the query behaves differently for the same data.

SELECT 
    item.tril_gid,
    CAST(SUBSTRING(comp.fr_productnumber,
            CHARINDEX('-', comp.fr_productnumber, 1) + 1,
                 CHARINDEX('-', comp.fr_productnumber,
            CHARINDEX('-', comp.fr_productnumber, 1) + 1) -
                 CHARINDEX('-', comp.fr_productnumber, 1) - 1) AS INT) AS comp.ProNum
FROM   
    SCQLI comp WITH(nolock)
LEFT JOIN 
    ffc ffc WITH(nolock) ON ffc.sc_quote_line_item = comp.tril_gid
LEFT JOIN 
    ffa ffa WITH(nolock) ON ffa.tril_gid = ffc.ffassembly
LEFT JOIN 
    scq item WITH(nolock) ON item.tril_gid = ffa.sc_quote_line_item
INNER JOIN 
    jde jde WITH(nolock) ON jde.tril_gid = item.fr_jde_order
INNER JOIN 
    frq frq WITH(nolock) ON frq.tril_gid = jde.frquoterevision
WHERE  
    comp.fr_jde_order = 'QFXZZBSHH1YRFZULBEBS3C4HULDV42VR' 
    AND comp.FR_ProductNumber <> ''

Your code is not written to safely ignore or use alternative logic in the expression that involves substring when it encounters values that do not match your assumption. More importanly, stop splattering your code with [nolock](https://www.sentryone.com/blog/aaronbertrand/bad-habits-nolock-everywhere). Also, your left joins are implicitly converted to inner joins due to the order of joining and the mixing of inner and outer joins. — SMor, Apr 25 '21 at 20:37

John Cappelletti · Answer 1 · 2021-04-25T20:52:17.400

The following illustrates two different approaches. The first is is ParseName() ... provided your data has no more than 4 elements, and the second uses a bit of JSON

Example

Declare @YourTable Table ([SomeCol] varchar(50))  
Insert Into @YourTable Values 
 ('ABC-123-ABCDEF')

Select SomeCol
      ,WithParseName = parsename(replace(SomeCol,'-','.'),2)
      ,WithJSON      = JSON_VALUE('["'+replace(SomeCol,'-','","')+'"]','$[1]')
 From @YourTable

Returns

SomeCol         WithParseName   WithJSON
ABC-123-ABCDEF  123             123

score 2 · Answer 2 · answered Apr 26 '21 at 00:39

First off, two things you should remember when doing string manipulation in SQL Server:

You should use CROSS APPLY to save one calculation and feed it into the next. This saves a lot of repetition, and is a good use of the DRY principle.
CHARINDEX can return 0 if it doesn't find the string, so this needs to be nulled out for safety.

Further points, as mentioned by others:

NOLOCK should be avoided unless you really know what you are doing, and are happy to get incorrect results.
Some of your LEFT JOINs don't make sense and should be INNER JOIN

Your query now becomes:

SELECT 
    item.tril_gid,
    TRY_CAST(SUBSTRING(comp.fr_productnumber,
            v1.firstDash + 1,
                 v2.SecondDash - v1.firstDash - 1
        ) AS INT) AS comp.ProNum
FROM   
    SCQLI comp
INNER JOIN 
    ffc ffc ON ffc.sc_quote_line_item = comp.tril_gid
INNER JOIN 
    ffa ffa ON ffa.tril_gid = ffc.ffassembly
INNER JOIN 
    scq item ON item.tril_gid = ffa.sc_quote_line_item
INNER JOIN 
    jde jde ON jde.tril_gid = item.fr_jde_order
INNER JOIN 
    frq frq ON frq.tril_gid = jde.frquoterevision
CROSS APPLY (VALUES(
    NULLIF(CHARINDEX('-', comp.fr_productnumber, 1), 0)
) ) v1(firstDash)
CROSS APPLY (VALUES(
    NULLIF(CHARINDEX('-', comp.fr_productnumber, v1.firstDash + 1), 0)
) ) v2(secondDash)
WHERE  
    comp.fr_jde_order = 'QFXZZBSHH1YRFZULBEBS3C4HULDV42VR' 
    AND comp.FR_ProductNumber <> ''

Why was your query only working some of the time, even on the same data?

The answer to that probably lies in parts of the query plan that we cannot see, namely at what point are the string functions calculated? (Note that the position of Compute Scalar in the plan does not tell you this.)

If they are calculated before filtering takes place then the query could fail if rows which should not appear in the result cause an error. There is no way to guarantee what the server might do, therefore you must add extra checks.

You just solved the exact same problem for me. Thanks! Can you explain a bit more about what CROSS APPLY, VALUES, and NULLIF are doing in your SQL? I guess NULLIF checks to see if the dash is in the substring and if not returns NULL and substring of NULL just returns null, likewise TRY_CAST of NULL returns NULL so those avoid the error being thrown? How does CROSS APPLY and VALUES work though? — James, Jan 23 '23 at 18:00
Correct, `NULLIF` must be used otherwise `CHARINDEX` could return `0` and mess up later calculations. `CROSS APPLY` is a set-returning operator: it "applies" the result per each row of the previous table. In this case, we are simply using it to maufacture a single-row single-column table (calculated again and again per row) in order to store intermediate calculations. As you can see, once you use `CROSS APPLY` the calculation becomes available later on. See also https://stackoverflow.com/a/65818648/14868997 — Charlieface, Jan 23 '23 at 21:29
As for my final point about errors, see also https://stackoverflow.com/a/73705161/14868997 and https://dba.stackexchange.com/a/301650/220697 and https://www.sql.kiwi/2012/09/compute-scalars-expressions-and-execution-plan-performance.html — Charlieface, Jan 23 '23 at 21:32

score 0 · Answer 3 · edited Apr 26 '21 at 05:47

I would double check the data that's giving you the error, when I run the following query I get the expected result. You might have a fr_productnumber that's invalid (missing a '-' or something). Do you have another sample you can provide? See below for example:

select 
     comp.fr_productnumber
    ,Cast(Substring(comp.fr_productnumber,
            Charindex('-', comp.fr_productnumber, 1) + 1,
                 Charindex('-', comp.fr_productnumber,
            Charindex('-', comp.fr_productnumber, 1) + 1) -
                 Charindex('-', comp.fr_productnumber, 1) - 1) AS INT) as [Result]
from
(
    select 'ABC-123-ABCDEF' as fr_productnumber
) as comp

which produces:

| fr_productnumber | Result |
| ---------------- | ------ |
|  ABC-123-ABCDEF  |  123   |

invalid length parameter passed to the left or substring function - Error is not happening consistently for the same data

3 Answers3

Linked

Related