Problem with case statement

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]<chudson007@.hotmail.com> wrote in message
news:1125938525.701908.180610@.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|||cheers simon|||(chudson007@.hotmail.com) writes:
> 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]

In SQL _ is a wildcard for exactly one occurrance of one characater. Thus
%_% matches anything but the empty string. Change to %[_]% to get what you
want.

...by the way, CASE is an expression, not a statement, in SQL.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql