Tutorial :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?


Solution:1

You are missing some right parrens.


Solution:2

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  

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »