whytheq whytheq - 6 months ago 24
SQL Question

CONTAINS unhappy with char(13)

Why won't

SSMS
process this script for me?

DECLARE @containsReturn AS CHAR(500) =
'ME@ME.co.uk;

YOU@YOU.co.uk;'


CREATE TABLE #x(ProblemColumn CHAR(500));
INSERT INTO #x VALUES(@containsReturn);

SELECT * FROM #X;


UPDATE #x
SET ProblemColumn = REPLACE(ProblemColumn, char(13), '')
WHERE CONTAINS(ProblemColumn, CHAR(13))


I get this error, focused on the final
CHAR(13)
in the
UPDATE
's
WHERE
clause:


Msg 102, Level 15, State 1, Line 14

Incorrect syntax near 'CHAR'.

Answer

One quick thing why you even care where clause in this case, just run the update statement like this

UPDATE  #x
SET     ProblemColumn = REPLACE(ProblemColumn, char(13), '')

it will do the right thing. it will perform better too

Comments