Can 'Like' be used in Case Statement??

Question

Hi All,

Can we use 'like' in case stetement as follows ??

select id, 
       name,
       (case when name like 'sa%' then ' ' else name end ) as fullname
from table1

Answer 1

0

Roshan,

yes you can.

Try this out:

DECLARE @TestTable TABLE (id int, NAME varchar(25)) 
INSERT INTO @TestTable
        (id, NAME)
VALUES  (0,'sandy'),
       (1,'paul')
;
SELECT  id,
        name,
        (CASE WHEN name LIKE 'sa%' THEN ' '
              ELSE name
         END) AS fullname
FROM    @TestTable

answered Oct 27 '10 at 01:51 PM

WilliamD
25.3k ● 16 ● 18 ● 41

(comments are locked)

Answer 2

0

Yes!

Did you have trouble running it against table1?

answered Oct 27 '10 at 01:51 PM

KenJ
12.4k ● 2 ● 10

I knew I'd be too slow to answer this one! (+1)

Oct 27 '10 at 01:53 PM Mark

I was striving for brevity!

Oct 27 '10 at 02:25 PM KenJ

@KenJ - brevity fail! you used 8 words too many there ;o)

Oct 27 '10 at 02:26 PM WilliamD

The query in the question was so good, I thought it was likely to have been run with issues before the question was asked.

Oct 27 '10 at 02:30 PM KenJ

@roshan If William's answer helped you, I am sure it did, please upvote it and then mark it as accepted. Since you asked the question, you are the only one at this time who can accept it. The way to accept the answer is to click on the box next to the voting thumbs. Other users do not have this box visible, but you do, because this is your question. When you accept the answer, it will change background. This makes it easier for the people who browse through existing questions to find helpful answers quickly.

Oct 27 '10 at 03:02 PM Oleg

(comments are locked)

Answer 3

0

...also the other way around

DECLARE @table1 TABLE(ID int, City char(3))
INSERT @table1
VALUES (1,'NYC'),(2,'LA'),(3,'SFO'),(4,'ATL'),(5,'SJC')

SELECT [ID]
      ,[City]
  FROM @table1
  WHERE City LIKE CASE WHEN ID = 1 
                       THEN 'N%' 
                       ELSE 'S%' END

answered Oct 27 '10 at 10:45 PM

Scot Hauder
5.7k ● 13 ● 15 ● 18

I wouldn't use a case in the WHERE clause like this, the performance impact is terrible because this condition will cause a scan of some sort, tablescan, clustered index scan or index scan, depending on existing indexes. A seek (without a key lookup) is prefferred.

Oct 27 '10 at 11:35 PM Håkan Winther

(comments are locked)

Can 'Like' be used in Case Statement??

Follow this question

Related Questions