SQL Syntax is still something I am learning. I am getting the error noted below the this snippet of code.
CASE WHEN LTRIM(RTRIM(cLehmanNo)) =' ' THEN NULL
WHEN cLehmanNo IS NOT NULL THEN REPLACE ( cLehmanNo,SUBSTRING (cLehmanNo,PATINDEX( '%[^a-zA-Z0-9 '''''']%',cLehmanNo),1), ' ' )
,CASE WHEN LTRIM(RTRIM(cMERS)) =' ' THEN NULL
WHEN cMERS IS NOT NULL THEN REPLACE ( cMERS,SUBSTRING (cMERS,PATINDEX( '%[^a-zA-Z0-9 '''''']%',cMERS),1), ' ' )
Msg 8133, Level 16, State 1, Line 1
None of the result expressions in a CASE specification can be NULL.
This happens when it can't infer the type.
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
SELECT CASE WHEN 1 = 2 THEN NULL ELSE REPLACE(NULL,'','') END a INTO t /*Creates column of datatype varchar(8000)*/