Convert RGB color to HEX Color using SQL

• thomashohner

  SSCrazy Eights

  Points: 9856

  More actions

  March 16, 2015 at 11:15 am

  #1784301

  Hello gents,

  So I started researching the Convert and Bitwise. Very interesting stuff. I also ran into another issue. Not all row colors go good with black text.

  So I used more google fu and found some Java script that weights the colors. So if I choose Black back ground then White text ect.

  This is my first stab at this so it will probably get reworked. If number is greater than 128 choose black font if less choose white. While not perfect it seems to make the text more readable now.

  ***These weight values were taken from google article and the explanation seemed greek to me but it seems to work.

  SELECT CONVERT(NUMERIC ,((a.Color & 0xFF)* .30) + (((a.color & 0xFF00) / 0x100)* .60) + (((a.Color & 0xFF0000) / 0x10000)* .115))

  ***SQL born on date Spring 2013:-)

• Eirikur Eiriksson

  SSC Guru

  Points: 182915

  More actions

  March 16, 2015 at 1:19 pm

  #1784334

  Quick possible solution/method, should give you an idea

  😎

  DECLARE @COLOURINT INT = 14745599;

  SELECT 'RED' AS C_IDX, ( @COLOURINT & 0xFF) AS VALUE, (( 0xFFFFFF ^ @COLOURINT) & 0xFF) AS O_VAL UNION ALL

  SELECT 'GREEN' AS C_IDX, ((@COLOURINT & 0xFF00) / 0x100) AS VALUE, (((0xFFFFFF ^ @COLOURINT) & 0xFF00) / 0x100) AS O_VAL UNION ALL

  SELECT 'BLUE' AS C_IDX, ((@COLOURINT & 0xFF0000) / 0x10000) AS VALUE, (((0xFFFFFF ^ @COLOURINT) & 0xFF0000) / 0x10000) AS O_VAL;

  Results

  C_IDX VALUE O_VAL

  ----- ----------- -----------

  RED 255 0

  GREEN 255 0

  BLUE 224 31

• mister.magoo

  SSC-Forever

  Points: 47174

  More actions

  March 16, 2015 at 5:03 pm

  #1784376

  thomashohner (3/16/2015)

  ---

  Hello gents,

  So I started researching the Convert and Bitwise. Very interesting stuff. I also ran into another issue. Not all row colors go good with black text.

  So I used more google fu and found some Java script that weights the colors. So if I choose Black back ground then White text ect.

  This is my first stab at this so it will probably get reworked. If number is greater than 128 choose black font if less choose white. While not perfect it seems to make the text more readable now.

  ***These weight values were taken from google article and the explanation seemed greek to me but it seems to work.

  SELECT CONVERT(NUMERIC ,((a.Color & 0xFF)* .30) + (((a.color & 0xFF00) / 0x100)* .60) + (((a.Color & 0xFF0000) / 0x10000)* .115))

  I have a slight variant on that which I use in .NET applications sometimes.

  It translates to SQL as this:

  SELECT

  CASE WHEN 1 - ((a.Color & 0xFF)* .299 + ((a.color & 0xFF00) / 0x100)* .587 + ((a.Color & 0xFF0000) / 0x10000)* .114) / 255.0 < 0.5

  THEN 'Black'

  ELSE 'White'

  END

  Again, much simpler to code and understand if the RGB values were held in separate columns, (but understood you don't have that right now).

  I find this gives me slightly better results in my tests, but what looks good is personal...

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]