Like operator in Case statement

Question

Post reply

Like operator in Case statement

jaffrey

Mr or Mrs. 500

Points: 530
More actions
November 25, 2010 at 4:14 am

#231926

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;

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 1

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

Clear Sky SQL
My Blog[/url]

Nakul Vachhrajani SSChampion Points: 10294 More actions · Answer 2

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

jaffrey Mr or Mrs. 500 Points: 530 More actions · Answer 3

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