469,283 Members | 2,297 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,283 developers. It's quick & easy.

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]

Sep 5 '05 #1
3 2950
<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
Sep 5 '05 #2
cheers simon

Sep 6 '05 #3
(ch********@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, es****@sommarskog.se

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

Sep 9 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

68 posts views Thread by Marco Bubke | last post: by
10 posts views Thread by JMorrell | last post: by
5 posts views Thread by Ritesh | last post: by
10 posts views Thread by Chih-Hsu Yen | last post: by
5 posts views Thread by Tim::.. | last post: by
8 posts views Thread by | last post: by
2 posts views Thread by scole954387 | last post: by
22 posts views Thread by b_r | last post: by
10 posts views Thread by amitabh.mehra | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.