how to identify special characters with a single case statement

hi All :

I have an insert statement which reads

INSERT Into TableA (Col1)

SELECT CASE WHEN Col1 LIKE '%[a-z]%' THEN 999999 ELSE Col1 END AS
Col1,
FROM TableB

**********************
TableA. Col1 has a datatype of Int.
TableB. Col1 has a datatype of varchar.

My insert is failing as it is trying to insert special character,
identifying a \ in the input column

How can i write a similar case statement to filter out special
characters and replace with 99999

Also can you refer me to a place where i can have a list of special
characters and if need be write mulitple case statements to filter
them out.

thanks
RS

Re: how to identify special characters with a single case statement

You can negate your logic here and instead of looking to catch all invalid
characters, look for all characters excluding the set of digit characters.
It will be something like this:

INSERT INTO TableA
(Col1)
SELECT
CASE WHEN Col1 LIKE '%[^0-9]%'
THEN 999999
ELSE Col1 END
FROM TableB;


HTH,

Plamen Ratchev
http://www.SQLStudio.com