#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    2
    Rep Power
    0

    Question Mapping int to strings


    I've got some ints (1-6) that I need to convert and map to say 1a, 2b, 3c, e.g. strings.

    For normal mapping to ints I'd just use

    SELECT SomeInts, case when SomeInts = 1 THEN 2 when SomeInts = 2 THEN 3 ELSE 999 END AS SomeIntsNEW FROM SomeTable

    But if I try to change the first THEN to '2a' I get this:

    Conversion failed when converting the varchar value '2y' to data type int.

    I've tried various variants over cast/convert but none worked.

    Whats the right syntax?
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,140
    Rep Power
    4274
    use CAST
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    2
    Rep Power
    0
    I tried that. Can you be more precise please?
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,140
    Rep Power
    4274
    Code:
    SELECT SomeInts
         , CASE SomeInts 
           WHEN 1 THEN CAST(2 AS VARCHAR)
           WHEN 2 THEN '3a'
           ELSE 999 END AS SomeIntsNEW 
      FROM SomeTable
    i think (didn't test) that this is only required if you put the numeric one first

    i.e. shouldn't need it here --
    Code:
    SELECT SomeInts
         , CASE SomeInts 
           WHEN 2 THEN '3a'
           WHEN 1 THEN 2
           ELSE 999 END AS SomeIntsNEW 
      FROM SomeTable
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo