CODEWITHSUNDEEP
sqlsql-server
Sinnerv
Sinnerv

Reputation: 263

SQL Convert number to a word

I'm writing a simple SELECT query to get retrieve a set of item numbers from a SQL table.

now from this table, i want to get the Item type which can be one of three. Make, Buy or undefined.

My issue is that this is indicated in a number in the database. My table has a column called ITMTYPE but says 1 or 2 or 3 for Make, Buy or undefined.

Could anyone advise me of a way to add a column to my select Query that can show the equivalent wording for Item Type please?

Below is what i'd like to see,

Item_Number Item_Type_Num   Item Type_val
A                1               Make
B                2               Buy
C                3               Undefined
D                2               Buy
E                1               Make

This third column is what i'd like to see. Thanks

Upvotes: 1

Views: 109

Answers (3)

TheGameiswar
TheGameiswar

Reputation: 28900

Use simple Case Expression.

select
    Item_Number,
    Item_Type_Num ,
    Case Item_Type_Num
       when 1 then 'Make'
       when 2 then 'Buy'
       when 3 then 'Undefined'
    else 'Unknown'
    end as 'Item_type_val'
from Yourtable

Upvotes: 1

Pwl256
Pwl256

Reputation: 56

CASE expression

   WHEN value_1 THEN result_1
   WHEN value_2 THEN result_2
   ...
   WHEN value_n THEN result_n

   ELSE result

END

select item_number
,case Item_Type_num
   when 1 then 'Make'
   when 2 then 'Buy'
   else 'Undefined
end

Upvotes: 0

sagi
sagi

Reputation: 40481

You can use CASE EXPRESSION :

SELECT t.Item_Number,t.ITMTYPE as Item_Type_Num,
       CASE WHEN t.ITMTYPE = 1 THEN 'Make'
            WHEN t.ITMTYPE = 2 THEN 'Buy'
            WHEN t.ITMTYPE = 3 THEN 'undefined'
       END as Item_Type_Val
FROM YourTable t

If you have daily needs with this column I'd suggest you add it to the table:

ALTER TABLE YourTable
  ADD Item_Val_Type VARCHAR2(10);

And then update it:

UPDATE YourTable t
SET t.Item_Val_Type = CASE WHEN t.ITMTYPE = 1 THEN 'Make'
                           WHEN t.ITMTYPE = 2 THEN 'Buy'
                           WHEN t.ITMTYPE = 3 THEN 'undefined'
                      END;

Upvotes: 3

Related Questions