CODEWITHSUNDEEP
sql-servermssql-jdbc
user3114967
user3114967

Reputation: 629

SQL Server : Invalid length parameter passed to the LEFT or SUBSTRING function

I'm trying to execute a SQL Server SUBSTRING query but its throwing error.

The query is:

select 
    substring(testcasename, 2, 5) val1, 
    substring(testcasename, 2, CHARINDEX(',', testcasename, 2) - 2) val2 
from 
    tce_lineno 
where 
    testcasename <> '' and project='proj001'

Error is:

Msg 537, Level 16, State 5, Line 1
Invalid length parameter passed to the LEFT or SUBSTRING function.

Is anything wrong in this query?

Upvotes: 0

Views: 2206

Answers (2)

user3380585
user3380585

Reputation: 173

Add this clause to where

And testcasename like '%,%'

Error happens because some of the records does not have comma (,) in it

Upvotes: 0

Pரதீப்
Pரதீப்

Reputation: 93694

As I mentioned in comments When testcasename does not have , then CHARINDEX(',',testcasename,2) will return 0 so Substring will fail.

To fix that use case statement, When , is not found use length of testcasename

SELECT Substring(testcasename, 2, 5)   val1,
       Substring(testcasename, 2, CASE
                                    WHEN Charindex(',', testcasename, 2) > 2 THEN Charindex(',', testcasename, 2) - 2
                                    ELSE Len(testcasename)
                                  END) val2
FROM   tce_lineno
WHERE  testcasename <> ''
       AND project = 'proj001'

Upvotes: 1

Related Questions