<ch********@hotmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...
With the syntax below, why is field1a not "A" if field1 does not
contain "_"
SELECT Field1a = CASE WHEN (field1 LIKE '%_%') THEN (charindex('_',
field1)) ELSE 'A' END, field1
FROM [Table1]
See LIKE and "Pattern Matching in Search Conditions" in Books Online - an
underscore is a wildcard for any single character, so you need to escape it
for a literal match:
LIKE '%[_]%'
Another issue is that CASE is an expression, so it can only return a single
data type - as you've written it, it could return either an int or a char,
so you would get a data type conversion error. See "Result Types" under CASE
in Books Online - you'll need to decide on a single return type, or perhaps
CAST the integer to a character type:
SELECT
Field1a = CASE
WHEN (field1 LIKE '%[_]%') THEN cast((charindex('_',field1)) as char(2))
ELSE 'A' END,
field1
FROM [Table1]
Simon