Like operator in Case statement

  • 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;

  • 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]

  • 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

  • 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