November 25, 2010 at 4:14 am
Hai everyone,
I am stuck up in implementing like operator in case statement.I have the table called emp it consists empid,name,contact_num columns.What i want to do is if number starts with '9' then print 'chennai' and rest of the city should be printed accordingly. I tried but dont know how to write query to getting the result.
I attached that case statement below ,Anybody have an idea please let me know ,Your are greatly appreciated in advance...
SELECT empid,contact,'CITY'=
CASE contact
WHEN like '9%' THEN 'Chennai'
WHEN like '8%' THEN 'Bangalore'
WHEN like '7%' THEN 'Mumbai'
WHEN like '6%' THEN 'Pune'
ELSE 'Oversease'
END
FROM emp;
November 25, 2010 at 4:29 am
Try this...
SELECT empid,contact,'CITY'=
CASE
WHEN contact like '9%' THEN 'Chennai'
WHEN contact like '8%' THEN 'Bangalore'
WHEN contact like '7%' THEN 'Mumbai'
WHEN contact like '6%' THEN 'Pune'
ELSE 'Oversease'
END
FROM emp;
BTW , LEFT is probably better used here
November 25, 2010 at 4:30 am
How does this look?
DECLARE @Table1 TABLE (empid INT,name NVARCHAR(50),contact_num NVARCHAR(50))
INSERT INTO @Table1 VALUES (1111,'U1',9119),
(2222,'U2',8118),
(3333,'U3',7117),
(4444,'U4',6116),
(5555,'U5',9116),
(6666,'U6',4114)
SELECT empid,name,'CITY'=
CASE
WHEN contact_num like '9%' THEN 'Chennai'
WHEN contact_num like '8%' THEN 'Bangalore'
WHEN contact_num like '7%' THEN 'Mumbai'
WHEN contact_num like '6%' THEN 'Pune'
ELSE 'Oversease'
END
FROM @Table1;
Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com
Follow me on
Twitter: @sqltwins
November 25, 2010 at 4:41 am
Dave Ballantyne (11/25/2010)
Try this...
SELECT empid,contact,'CITY'=
CASE
WHEN contact like '9%' THEN 'Chennai'
WHEN contact like '8%' THEN 'Bangalore'
WHEN contact like '7%' THEN 'Mumbai'
WHEN contact like '6%' THEN 'Pune'
ELSE 'Oversease'
END
FROM emp;
BTW , LEFT is probably better used here
Thanks lot works fine
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply