question

bkeane avatar image
bkeane asked

Wildcards in Select Statement Using CASE won't work

Why won't my wildcards work? I want to find any strType that begins SUS and list a new column strSUS that will either say Suspended or have a null for each record. I keep running into incorrect syntax errors, and if I create a subquery: WHEN (SELECT t.strType FROM t WHERE t.strType LIKE 'SUS%%%' I get an error about not using = or <>. Please let me know if you have a solution to my problem SELECT t.lngKey, CASE t.strType WHEN strType LIKE 'SUS%%%' THEN 'Suspended' ELSE '' END AS strSus FROM tblTaskOpen t
selectwildcardcase
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
ThomasRushton avatar image
ThomasRushton answered
If you just write your query like this: SELECT t.IngKey , CASE WHEN strType LIKE 'SUS%' THEN 'Suspended' ELSE '' END AS strSus FROM tblTaskOpen t You'll get the result you need. The [`CASE`][1] statement works either like this: CASE < > WHEN < > THEN < > WHEN < > THEN < > ... ELSE < > -- optional END Or like this: CASE WHEN Expression1 THEN Result1 WHEN Expression2 THEN Result2 ... ELSE DefaultResult END What you were doing was attempting to combine the two methods of operation. [1]: http://technet.microsoft.com/en-us/library/ms181765.aspx
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@bkeane, I note that you used the wildcard '%' character three times in your query - it can represent any number of characters, so there is no need to use it multiple times. If you wanted to represent one and only one character (but any character) you can use underscore (_).
2 Likes 2 ·
Ah, I see.. Thanks for the help!
0 Likes 0 ·
You're welcome. If it's any consolation, I've tried doing the same myself... ;-)
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.