x

Can 'Like' be used in Case Statement??

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
more ▼

asked Oct 27, 2010 at 01:46 PM in Default

avatar image

roshan
137 7 8 13

I think you deserve a [self learner] badge for that question

Oct 27, 2010 at 10:51 PM Scot Hauder
(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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
more ▼

answered Oct 27, 2010 at 01:51 PM

avatar image

WilliamD
26.2k 18 34 48

(comments are locked)
10|1200 characters needed characters left

Yes!

Did you have trouble running it against table1?

more ▼

answered Oct 27, 2010 at 01:51 PM

avatar image

KenJ
24.5k 3 10 19

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

Oct 27, 2010 at 01:53 PM Mark

I was striving for brevity!

Oct 27, 2010 at 02:25 PM KenJ

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

Oct 27, 2010 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, 2010 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, 2010 at 03:02 PM Oleg
(comments are locked)
10|1200 characters needed characters left

...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
more ▼

answered Oct 27, 2010 at 10:45 PM

avatar image

Scot Hauder
6.4k 13 16 22

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, 2010 at 11:35 PM Håkan Winther
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1067
x23
x13

asked: Oct 27, 2010 at 01:46 PM

Seen: 7406 times

Last Updated: Oct 27, 2010 at 03:01 PM

Copyright 2016 Redgate Software. Privacy Policy