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
Yes! Did you have trouble running it against table1?
@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.
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
...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
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.
