CODEWITHSUNDEEP
sql-serversql-server-2005colorsrgb
Curtis
Curtis

Reputation: 103358

Convert Colour Names into RGB

I have a table of records which each store a Colour Name. e.g:

Product    |  Colour
-------------------
Product A  |  Blue
Product B  |  Black

I have added 3 new columns: R,G & B. How can I convert the Colours into RGB values using a single SQL query?

Upvotes: 3

Views: 2171

Answers (3)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

;with Colours(Name, R, G, B) as
(
  select 'White',   255, 255, 255 union all
  select 'Silver',  192, 192, 192 union all
  select 'Gray',    128, 128, 128 union all
  select 'Black',   0  , 0  , 0   union all
  select 'Red',     255, 0  , 0   union all
  select 'Maroon',  128, 0  , 0   union all
  select 'Yellow',  255, 255, 0   union all
  select 'Olive',   128, 128, 0   union all
  select 'Lime',    0  , 255, 0   union all
  select 'Green',   0  , 128, 0   union all
  select 'Aqua',    0  , 255, 255 union all
  select 'Teal',    0  , 128, 128 union all
  select 'Blue',    0  , 0  , 255 union all
  select 'Navy',    0  , 0  , 128 union all
  select 'Fuchsia', 255, 0  , 255 union all
  select 'Purple',  128, 0  , 128
)
update P set
  R = C.R,
  G = C.G,
  B = C.B
from products as P
  inner join Colours as C
    on P.Colour = C.Name

Upvotes: 3

Serg
Serg

Reputation: 2427

You need to decode colours to RGB values in the same query which is used to update your table. While you are not using any functions, variables or another tables this can be done using CASE expression. This is not elegant solution but it will work.

UPDATE products
SET
    r = (CASE colour
            WHEN 'Black' THEN 0
            WHEN 'Red' THEN 255
            WHEN 'Green' THEN 0
            WHEN 'Blue' THEN 0
            WHEN 'White' THEN 255
            ELSE NULL
        END),
    g = (CASE colour
            WHEN 'Black' THEN 0
            WHEN 'Red' THEN 0
            WHEN 'Green' THEN 255
            WHEN 'Blue' THEN 0
            WHEN 'White' THEN 255
            ELSE NULL
        END),
    b = (CASE colour
            WHEN 'Black' THEN 0
            WHEN 'Red' THEN 0
            WHEN 'Green' THEN 0
            WHEN 'Blue' THEN 255
            WHEN 'White' THEN 255
            ELSE NULL
        END)

Or maybe in the following way:

UPDATE products
SET
    r = (CASE
            WHEN Colour IN ('Black', 'Green', 'Blue') THEN 0
            WHEN Colour IN ('Red', 'White') THEN 255
            ELSE NULL
        END),
    g = (CASE
            WHEN Colour IN ('Black', 'Red', 'Blue') THEN 0
            WHEN Colour IN ('Green', 'White') THEN 255
            ELSE NULL
        END),
    b = (CASE
            WHEN Colour IN ('Black', 'Red', 'Green') THEN 0
            WHEN Colour IN ('Blue', 'White') THEN 255
            ELSE NULL
        END)

Upvotes: 1

Alex K.
Alex K.

Reputation: 175776

You will need to add the RGB values for the corresponding name yourself, there is no way to derive that information from a colour name (after all what constitutes "Dark Blue"?).

You could use the CSS names for hints to build the initial look-up table.

Upvotes: 2

Related Questions