CASE ... WHEN statements in SQL syntax 1

[StevenK]

I'm running queries against SQL server 7.0 databases and am looking to achieve the following.
I want to output the Customer Type (CustType) along with a bit value indicating if the customer type starts with the letter 'A' for example.
If I run the command :
'SELECT CustType,
CASE CustType WHEN 'AG' THEN 1 ELSE 0 END AS NewCol
FROM [Customers]
ORDER BY CustType' I get a result set something like :
CustType NewCol
AA 0
AB 0
AG 1
BA 0
BB 0
BH 0
Extending this to an instance when the code is 'LIKE' a string -i.e. using the syntax :
'SELECT CustType,
CASE CustType WHEN LIKE 'A%' THEN 1 ELSE 0 END AS NewCol
FROM [Customers]'
This produces an error indicating incorrect syntax near LIKE.
Can anyone suggest a way of achieving what I need - having got the output of 1 or 0 I'm looking at just a simple case of CAST'ing the field as a bit data-type.
Thanks in advance.
Steve

 

[foxdev]

Use the boolean expression structure with CASE:

[tt]SELECT CustType,
CASE
WHEN CustType LIKE 'A%'
THEN 1
ELSE 0
END AS NewCol [/tt]
Robert Bradley

http://www.lowcostprints.com

http://www.foxdev.com

 

[StevenK]

foxdev : Thanks for that.
No sooner had I posted the message that I found a workaround myself - using a statement involving 'CASE SUBSTRING(CustType, 1, 1) WHEN 'A' THEN 1 ELSE 0 END AS NewCol'.
Thanks again though.