CODEWITHSUNDEEP
sqlsql-server
realtek
realtek

Reputation: 841

SQL CASE fails when using left() function

I have this SQL CASE statement which checked that a value contains a comma and then performs the left and charindex function on it.

However the query fails because it looks like its still trying to execute the left() function on it.

select 
   case 
      when '12560' like '%,%' 
        then left('12560',charindex(',', '12560', 0) - 1) 
        else '12560'  
   end

I basically may or may not have a comma in the parameter and therefore may or may not want to do a left on it.

Can this be done in a single case statement? If not I will have to write function to do this with an IF statement.

The error is:

Msg 536, Level 16, State 1, Line 1
Invalid length parameter passed to the left function.

Thanks

Upvotes: 2

Views: 3988

Answers (1)

brazilianldsjaguar
brazilianldsjaguar

Reputation: 1449

You can add an ISNULL() statement around the CHARINDEX.

Example:

select 
   case 
      when '12560' like '%,%' 
        then left('12560',ISNULL(charindex(',', '12560', 0),1) - 1) 
        else '12560'  
   end

SQLFiddle with demo

EDIT

For the sake of completeness, you could wrap this in a variable and get the same result (without the ISNULL), due to the constant folding that Alex mentioned in the comments:

DECLARE @str VARCHAR(5) = '12560'
select 
   case 
      when @str like '%,%' 
        then left(@str,charindex(',', @str, 0) - 1) 
        else @str 
   end

Upvotes: 2

Related Questions