TSQL CASE WHEN THEN SYNTAX - USING REPLACE

Question

This actually applies to a prior question, TSQL 2008 USING LTRIM(RTRIM and Still Have Spaces

I am at the point of writing a very lengthy SELECT statement using OMG PONIES statement to remove NON NULL non visible characters

 (WHEN PropStreetAddr is NOT NULL THEN
    (SELECT LTRIM(RTRIM((REPLACE(PropStreetAddr, 
                                 SUBSTRING(PropStreetAddr, 
                                           PATINDEX('%[^a-zA-Z0-9 '''''']%', 
                                           PropStreetAddr), 
                                 1), '') AS PropStreetAddr)

Query:

SELECT 
  CASE WHEN LOAN_NUMBER IS NOT NULL THEN 
     REPLACE( LOAN_NUMBER,SUBSTRING (LOAN_NUMBER,PATINDEX( ' %[^a-zA-Z0-9 '''''']% ' , ' ' ) as LOAN_NUMBER.
 ,CASE WHEN MERS_ID IS NOT NULL THEN 
     REPLACE(  MERS_ID,SUBSTRING (MERS_ID,PATINDEX( '  %[^a-zA-Z0-9 '''''']% ' , ' ' ) as MERS_ID 
 ...127 more lines of similar statements

As soon as I check the syntax I receive this error pointing to the first Case statement after SELECT:

Msg 156, Level 15, State 1, Line 143 Incorrect syntax near the keyword 'as'.

Could someone help me understand what I am missing?

Will A · Accepted Answer · 2010-08-02 23:17:08Z

5

You're missing the END from your case statements. You look like you could do with ELSEs in there as well, although these are not compulsory - if left off and nothing matches then you'll get a NULL.

CASE
 WHEN something then value1
 WHEN somethingelse then value2
 ELSE value3
END

answered Aug 2, 2010 at 23:17

Will A

25k5 gold badges54 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Tahbaza Over a year ago

+1 ...and the REPLACE(SUBSTRING(PATINDEX( is not making logical sense at the moment...

JMS49 Over a year ago

OMG, I have been looking at this code too long! I appreciate your comments & heads up.. Here is what see I need to fix this.. So very sorry I missed this earlier... SELECT CASE WHEN LOAN_NUMBER IS NOT NULL THEN REPLACE( LOAN_NUMBER,SUBSTRING (LOAN_NUMBER,PATINDEX( ' %[^a-zA-Z0-9 '''''']% ' ,LOAN_NUMBER), 1) , ' ' ) End as LOAN_NUMBER and so on

buckbova · Accepted Answer · 2010-08-02 23:18:54Z

2

You are missing some right parrens.

answered Aug 2, 2010 at 23:18

buckbova

1,2336 silver badges11 bronze badges

Collectives™ on Stack Overflow

TSQL CASE WHEN THEN SYNTAX - USING REPLACE

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related