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

roshan gravatar image

roshan
137 7 8 10

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

WilliamD gravatar image

WilliamD
25.9k 17 19 41

(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

KenJ gravatar image

KenJ
20k 1 3 12

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

Scot Hauder gravatar image

Scot Hauder
6k 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, 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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x986
x40
x22

asked: Oct 27, 2010 at 01:46 PM

Seen: 4846 times

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