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;

• Dave Ballantyne

  SSC-Dedicated

  Points: 33667

  More actions

  November 25, 2010 at 4:29 am

  #1255515

  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

  November 25, 2010 at 4:30 am

  #1255517

  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

  November 25, 2010 at 4:41 am

  #1255527

  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