Posted by Simon Hayes on 09/05/05 19:50

<chudson007@hotmail.com> wrote in message
news:1125938525.701908.180610@f14g2000cwb.googlegroups.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

[Back to original message]