CODEWITHSUNDEEP
sqlsql-serversubstring
Subin Jacob
Subin Jacob

Reputation: 4864

SQL Error in Non Case condition of CASE WHEN clause

I tried executing the following SQL statement.

SELECT CASE WHEN CHARINDEX('~','test.pdf') > 0 
            THEN SUBSTRING('test.pdf',CHARINDEX('~', 'test.pdf'), -10)
            ELSE NULL
       END

This resulted in an error 'Invalid length parameter passed to the substring function.'. However, this was not expected because it is not going to execute anyway.

This query is a simplified version of my requirement. Actually we are computing the value length for the substring. The real scenario is also given below :

SELECT CASE
            WHEN CHARINDEX('~', 'test.pdf') > 0 THEN SUBSTRING('test.pdf', CHARINDEX('~', 'test.pdf') + 1, CHARINDEX('~', 'test.pdf', (CHARINDEX('~', 'test.pdf', 1)) + 1) - CHARINDEX('~', 'test.pdf') - 1)
            ELSE NULL
       END;

In the example its hardcoded as 'test.pdf' but in real scenario it would be values like '111111~22222~33333~4444.pdf' from Table column. Also, I'm not sure this file name should always follow this format. Hence, a validation is required.

Actually, the computation for length is quite expensive, and don't want to use it twice in this query.

Upvotes: 0

Views: 89

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You have passed -10 as a constant to substring(). This function does not allow negative values for the third argument:

length

Is a positive integer or bigint expression that specifies how many characters of the expression will be returned. If length is negative, an error is generated and the statement is terminated. If the sum of start and length is greater than the number of characters in expression, the whole value expression beginning at start is returned.

SQL Server catches this problem during the compile phase. This has nothing to do with CASE expression evaluation, but with parsing the expressions.

Upvotes: 2

Related Questions