question

roshan avatar image
roshan asked

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
t-sqlcase-statementlike
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I think you deserve a [self learner] badge for that question
1 Like 1 ·
KenJ avatar image
KenJ answered
Yes! Did you have trouble running it against table1?
6 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@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.
1 Like 1 ·
I knew I'd be too slow to answer this one! (+1)
0 Likes 0 ·
I was striving for brevity!
0 Likes 0 ·
@KenJ - brevity fail! you used 8 words too many there ;o)
0 Likes 0 ·
The query in the question was so good, I thought it was likely to have been run with issues before the question was asked.
0 Likes 0 ·
@ken - Thanks, @WilliamD - Thanks for explaining with an example. It worked!!!!!
0 Likes 0 ·
WilliamD avatar image
WilliamD answered
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
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Scot Hauder avatar image
Scot Hauder answered
...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
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.