Migrate rgb values to HSL and hex in MYSQL db

Question

I have a mysql db that looks like this

target_color       
'rgb(200,200,200)'
'rgb(190,29,209)'

and I need to produce a db that looks like this

target_hue   ~   target_saturation  ~ target_lightness   ~   hex_value
10          ~    40        ~         40           ~         a567ff

The main problem I am running into is the parsing of the rgb(x,y,z) to extract the values. I thought that a regex would do the trick, but they are not supported in mysql...tried substrint() and substring_index() as well, but got totally tangled...

Answer 1

I used this to convert csv rgb values directly into hex.

UPDATE table1 ami
JOIN 
(
SELECT id, value 
, SUBSTRING(value, 1, LOCATE(',', value)-1) as R
, SUBSTRING(value, LOCATE(',', value)+1, LOCATE(',', value, LOCATE(',', value)+1) - LOCATE(',', value) - 1) G
, SUBSTRING(value, LOCATE(',', value, LOCATE(',', value)+1)+1) B
from table1 ai
where locate(',', value) > 0
) tbl ON ami.id = tbl.id
SET ami.value = CONCAT(
 LPAD(CONV(tbl.R, 10, 16),2,'0') 
, LPAD(CONV(tbl.G, 10, 16),2,'0')
, LPAD(CONV(tbl.B, 10, 16),2,'0'));

Obviously change the table names :) Hope this helps someone else.

Answer 2

You can use the SUBSTRING_INDEX function to pull out the individual integer values between the commas.

SET @rgb := 'rgb(190,29,209)' ;

SELECT @rgb
     , @rgbv := SUBSTRING_INDEX(SUBSTRING_INDEX(@rgb,'rgb(',-1),')',1) AS `@rgbv`
     , @v1 := SUBSTRING_INDEX(@rgbv,',',1)                             AS `@v1`
     , @v2 := SUBSTRING_INDEX(SUBSTRING_INDEX(@rgbv,',',2),',',-1)     AS `@v2`
     , @v3 := SUBSTRING_INDEX(SUBSTRING_INDEX(@rgbv,',',3),',',-1)     AS `@v3`
     , 0 + @v1  AS R
     , 0 + @v2  AS G
     , 0 + @v3  AS B

The last three columns in the SELECT list demonstrate that the user variables can be used in other (subsequent) expressions, in this case, adding them to an integer value of 0 to return them as integers.

Unfortunately, MySQL doesn't provide GREATEST and LEAST functions. Those would be convenient, so you could get a value for Hue with an expression like this:

GREATEST(0+@v1,0+@v2,0+@v3) - LEAST(0+@v1,0+@v2,0+@v3)

You can "roll your own" greatest and least functions for the three values:

IF(0+@v1>0+@v2
  ,IF(0+@v1>0+@v3,0+@v1,IF(0+@v2>0+@v3,0+@v2,0+@v3))
  ,IF(0+@v2>0+@v3,0+@v2,0+@v3)
  ) AS `max(R,G,B)`

IF(0+@v1<0+@v2
  ,IF(0+@v1<0+@v3,0+@v1,IF(0+@v2<0+@v3,0+@v2,0+@v3))
  ,IF(0+@v2<0+@v3,0+@v2,0+@v3)
  ) AS `min(R,G,B)`

---

From a table containing a column named rgb, a query might look like this:

SELECT s.R
     , s.G
     , s.B
     , IF(s.R>s.G,IF(s.R>s.B,s.R,s.B),IF(s.G>s.B,s.G,s.B)) AS `max(R,G,B)`
     , IF(s.R<s.G,IF(s.R<s.B,s.R,s.B),IF(s.G<s.B,s.G,s.B)) AS `min(R,G,B)`
  FROM (
         SELECT t.rgb
              , @rgbv := SUBSTRING_INDEX(SUBSTRING_INDEX(t.rgb,'rgb(',-1),')',1) AS `@rgbv`
              , @v1 := SUBSTRING_INDEX(@rgbv,',',1)                              AS `@v1`
              , @v2 := SUBSTRING_INDEX(SUBSTRING_INDEX(@rgbv,',',2),',',-1)      AS `@v2`
              , @v3 := SUBSTRING_INDEX(SUBSTRING_INDEX(@rgbv,',',3),',',-1)      AS `@v3`
              , 0 + @v1  AS v1
              , 0 + @v2  AS v2
              , 0 + @v3  AS v3
           FROM mytable t
       ) s

Answer 3

Given the field that contains 'rgb(190,29,209)' is called rgb , then you can extract those three values with this sql:

select 
   substring(rgb, locate('(',rgb)+1,locate(',',rgb) -1 - locate('(',rgb)) as red,
   substring(substring_index(rgb,',',2) , locate(',',rgb)+1) as green,
   substring(rgb, locate(',',rgb, locate(',',rgb)+1 )+1 , locate(')',rgb) 
                   -1 - locate(',',rgb, locate(',',rgb)+1 )) as blue

that would output this result set:

red  green  blue
190  29     209