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?
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
You are missing some right parrens.
精彩评论