TSQL CASE LTRIM (RTRIM NULL

Question

SQL Syntax is still something I am learning. I am getting the error noted below the this snippet of code.

SELECT
 CASE  WHEN    LTRIM(RTRIM(cLehmanNo)) =' ' THEN NULL 
       WHEN cLehmanNo IS NOT NULL  THEN REPLACE ( cLehmanNo,SUBSTRING (cLehmanNo,PATINDEX( '%[^a-zA-Z0-9 '''''']%',cLehmanNo),1), ' ' )
 END asLOAN_NUMBER
,CASE  WHEN    LTRIM(RTRIM(cMERS)) =' ' THEN NULL 
       WHEN cMERS IS NOT NULL  THEN REPLACE ( cMERS,SUBSTRING (cMERS,PATINDEX( '%[^a-zA-Z0-9 '''''']%',cMERS),1), ' ' )
 END asMERS_ID

and 100+ more of same.

Msg 8133, Level 16, State 1, Line 1
None of the result expressions in a CASE specification can be NULL.

What am I doing wrong? How do I keep the gist of the statement and not get this crazy error?

Answer 1

You need to convert NULL to a correct type matching the overall values, e.g. CONVERT(VARCHAR(10), NULL), otherwise the server can't deduce which type to make the resulting value.

Answer 2

This happens when it can't infer the type.

e.g.

SELECT CASE WHEN 1 = 2 THEN NULL ELSE NULL END 

But this works

SELECT CASE WHEN 1 = 2 THEN NULL ELSE replace(NULL,'','') END

so I doubt the error is from the code you have shown us (You are using string functions and the following quick test shows that it will assume that to be varchar(8000))

SELECT CASE WHEN 1 = 2 THEN NULL ELSE REPLACE(NULL,'','') END a
INTO t /*Creates column of datatype varchar(8000)*/

Answer 3

The error message actually means that all results in one of your case expressions are null. You have an expression like:

case when something then null when something then null end

At least one of the results has to be something other than null. You could circumvent this, but most likely there is a mistake in the query, as a case exression that always returns the same result is pointless.

The error message has been changed to:

At least one of the result expressions in a CASE specification must be an expression other than the NULL constant.

Answer 4

SELECT
 CASE  WHEN    LTRIM(RTRIM(cLehmanNo)) =' ' THEN NULL 
       WHEN cLehmanNo IS NOT NULL  THEN REPLACE ( cLehmanNo,SUBSTRING (cLehmanNo,PATINDEX( '%[^a-zA-Z0-9 '''''']%',cLehmanNo),1), ' ' )

ELSE ''

 END asLOAN_NUMBER
,CASE  WHEN    LTRIM(RTRIM(cMERS)) =' ' THEN NULL 
       WHEN cMERS IS NOT NULL  THEN REPLACE ( cMERS,SUBSTRING (cMERS,PATINDEX( '%[^a-zA-Z0-9 '''''']%',cMERS),1), ' ' )

ELSE ''

 END asMERS_ID

Answer 5

On a slight tangent, this looks like a good case for using a User Defined Function:

create function dbo.PrepareString (@input varchar(100))
returns varchar(100)
AS
begin
    set @input = ltrim(ltrim(isnull(@input, '')))
    if( @input = '' )
        return null
    return replace(@input, substring(@input, patindex('%[^a-zA-Z0-9 '''''']%', @input), 1), ' ')
end

Then your select statement gets really simple:

select
    dbo.PrepareString(cLehmanNo) as asLOAN_NUMBER,
    dbo.PrepareString(cMERS) as asMERS_ID

If there are any changes later down the line, you only have to modify the one function and all columns in your select statement will return the updated result.