CASE statement using wildcard

im trying to use wildcard in CASE statement with no luck.

im use this:

SELECT CASE tb001
WHEN '%price%' then 'Price'
ELSE 'No price'

the colum is varchar

The expected result would be 'Price' in every row that contains price (with wildcard around) but every line shows 'No price'
Everything works fine if i don't use wildcard and put in the whole string.

thx in advance //MrTry (not tested)
SELECT
CASE
WHEN tb001 like '%price%' then 'Price'
ELSE 'No price'
END
FROM ...|||works like charm... thx alot

Case statement 'Operator' in Where clause

i have a sp like this
--
@.operator int --(1=smaller,2=bigger)
@.price decimal(18,2)
select id,price
from company
where (price case @.operator when 1 then <@.price when 2 then >@.price end )
how can i accomplish this?
thnxdeclare @.operator int,
@.price decimal(18,2)
select ID, Price
from Company
where (price < @.price and @.operator = 1 )
and (price > @.price and @.operator = 2)
But if you have this in a Store procedure
declare @.operator int,
@.price decimal(18,2)
if @.operator = 1
begin
select ID, Price
from Company
where price < @.price
end
else if @.operator = 2
begin
select ID, Price
from Company
where price > @.price
end
Also with the logic that you have if the price is the same you will
never see it.
So you need to do this if was in procedure
declare @.operator int,
@.price decimal(18,2)
if @.operator = 1
begin
select ID, Price
from Company
where price <= @.price
end
else if @.operator = 2
begin
select ID, Price
from Company
where price >= @.price
end|||On Wed, 25 Jan 2006 13:38:01 -0800, henk wrote:

>i have a sp like this
>--
>@.operator int --(1=smaller,2=bigger)
>@.price decimal(18,2)
>select id,price
>from company
>where (price case @.operator when 1 then <@.price when 2 then >@.price end )
>--
>how can i accomplish this?
>thnx
Hi Henk,
IF @.operator = 1
SELECT id, price
FROM company
WHERE price < @.price
ELSE
SELECT id, price
FROM company
WHERE price > @.price
Hugo Kornelis, SQL Server MVP|||You can use either options as follows
declare @.operator int --(1=smaller,2=bigger)
declare @.price decimal(18,2)
set @.operator = 1 -- 2
set @.price = 10.05
-- Option A
declare @.cmd varchar(8000)
set @.cmd = 'select id, price from company where price '+ case @.operator when
1 then '< @.price' else '> @.price' end
exec (@.cmd)
-- Option B
if @.operator = 1
select id
,price
from company
where price < @.price
if @.operator = 2
select id
,price
from company
where price >@.price
"henk" wrote:

> i have a sp like this
> --
> @.operator int --(1=smaller,2=bigger)
> @.price decimal(18,2)
> select id,price
> from company
> where (price case @.operator when 1 then <@.price when 2 then >@.price end )
> --
> how can i accomplish this?
> thnx
>|||thanks for your reply
i realy would like to do this in one statment without making use of if-else.
the reason behind this is that i have multiple operators for multiple
columns and it realy gets complex with 4 or 5 operators.
is it possible or do i have to use the if-else senario?
"Amiller" wrote:

> declare @.operator int,
> @.price decimal(18,2)
>
> select ID, Price
> from Company
> where (price < @.price and @.operator = 1 )
> and (price > @.price and @.operator = 2)
> But if you have this in a Store procedure
> declare @.operator int,
> @.price decimal(18,2)
>
> if @.operator = 1
> begin
> select ID, Price
> from Company
> where price < @.price
> end
> else if @.operator = 2
> begin
> select ID, Price
> from Company
> where price > @.price
> end
>
> Also with the logic that you have if the price is the same you will
> never see it.
> So you need to do this if was in procedure
> declare @.operator int,
> @.price decimal(18,2)
>
> if @.operator = 1
> begin
> select ID, Price
> from Company
> where price <= @.price
> end
> else if @.operator = 2
> begin
> select ID, Price
> from Company
> where price >= @.price
> end
>|||DECLARE @.operator int
DECLARE @.price decimal (18,2)
SET @.price = 17.99
--To find items that are less than @.price, set @.operator to 1
--To find items that are more than @.price, set @.operator to 2
SELECT id, price
FROM company
WHERE
CASE
WHEN price - @.price > 0 THEN 2
WHEN price - @.price <= 0 THEN 1
END
= @.operator
The following was tested on Northwind
USE Northwind
GO
DECLARE @.operator int
DECLARE @.price money
SET @.operator = 1
SET @.price = 17.95
select productid, unitprice
FROM Products
WHERE
CASE
WHEN UnitPrice > @.price THEN 2
WHEN UnitPrice < @.price THEN 1
END = @.operator
"henk" wrote:

> i have a sp like this
> --
> @.operator int --(1=smaller,2=bigger)
> @.price decimal(18,2)
> select id,price
> from company
> where (price case @.operator when 1 then <@.price when 2 then >@.price end )
> --
> how can i accomplish this?
> thnx
>|||I am not sure if the first statement will work i have never tried
something like that. I would go with the if else and comment all layers
so you know what one does what.|||thank you all for your helpfull replys.
i think i go with the Mark 's solution. that is exactly what i wanted.
thank you once again.
"Mark Williams" wrote:
> DECLARE @.operator int
> DECLARE @.price decimal (18,2)
> SET @.price = 17.99
> --To find items that are less than @.price, set @.operator to 1
> --To find items that are more than @.price, set @.operator to 2
> SELECT id, price
> FROM company
> WHERE
> CASE
> WHEN price - @.price > 0 THEN 2
> WHEN price - @.price <= 0 THEN 1
> END
> = @.operator
>
> The following was tested on Northwind
> USE Northwind
> GO
> DECLARE @.operator int
> DECLARE @.price money
> SET @.operator = 1
> SET @.price = 17.95
> select productid, unitprice
> FROM Products
> WHERE
> CASE
> WHEN UnitPrice > @.price THEN 2
> WHEN UnitPrice < @.price THEN 1
> END = @.operator
> --
>
> "henk" wrote:
>|||Just to clean things up
DECLARE @.operator int
DECLARE @.price decimal (18,2)
SET @.price = 17.99
--To find items that are less than @.price, set @.operator to 1
--To find items that are more than @.price, set @.operator to 2
SELECT id, price
FROM company
WHERE
CASE
WHEN price > @.price THEN 2
WHEN price <= @.price THEN 1
END
= @.operator
Not sure why I did the whole price - @.price > 0 thing when the above is much
cleaner.
"henk" wrote:

> thank you all for your helpfull replys.
> i think i go with the Mark 's solution. that is exactly what i wanted.
> thank you once again.
>|||yeah i got it,
your the man man!!!!:)sql

Case Expression in SQL Server 2000

I have to use the Case expression in my query, so I search arround the web and got the following:

SELECT title, price,
Budget = CASE price
WHEN price > 20.00 THEN 'Expensive'
WHEN price BETWEEN 10.00 AND 19.99 THEN 'Moderate'
WHEN price < 10.00 THEN 'Inexpensive'
ELSE 'Unknown'
END,
FROM titles

It should run OK base on my research in the internet. But my SQL Server gave me error:

syntax error arround '>'.

I did several search and many people can use the ">" sign or "<" sign in the Case expression, but I just can't use it in my SQL Server, I can't even use any boolean expression, I can only use values.

can anyone help me out? My SQL Server Version is SQL Server 2000 Sevice Pack 4.

Thanks!

Try this:

SELECT title, price, CASE price AS Budget
WHEN > 20.00 THEN 'Expensive'
WHEN BETWEEN 10.00 AND 19.99 THEN 'Moderate'
WHEN < 10.00 THEN 'Inexpensive'
ELSE 'Unknown'
END,
FROM titles

|||

come out error too.

Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '>'.

|||

SELECT title, price,

CASE

WHEN price > 20.00 THEN 'Expensive'
WHEN price BETWEEN 10.00 AND 19.99 THEN 'Moderate'
WHEN price < 10.00 THEN 'Inexpensive'
ELSE 'Unknown'
END AS Budget
FROM titles

|||

The case expression has 2 formats:

Simple CASE function:

CASEinput_expression
WHENwhen_expressionTHENresult_expression
[ ...n]
[
ELSEelse_result_expression
]
END

Searched CASE function:

CASE
WHENBoolean_expression THENresult_expression
[ ...n]
[
ELSEelse_result_expression
]
END

So you're using the simple CASE function when you add 'price' column following CASE keyword. Note this will evaluateinput_expression, and then, in the order specified, evaluatesinput_expression=when_expression for each WHEN clause. That's why CASE price WHEN price>N doesn't work. For more information about CASE, you can take a look at:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_5t9v.asp

|||

Great, Thanks so much for you answer! funny thing is I searched so many web sites, they all have bad Case expression..