snippetsqlModerate
Convert int to RGB or hex color
Viewed 0 times
convertcolorhexrgbint
Problem
I'm working with a database where colors are stored as integers. For some unknown reason, they are negative integers actually.
Now, I need to use them in the CSS
I tried converting
Now, I need to use them in the CSS
background-color property, which accepts color names, RGB colors or hexadecimal colors. The problem is I don't know how to convert them correctly to get an acceptable string value.I tried converting
-65280 with CONVERT(VARBINARY(8), abs(S.Color)) and I'm obtaining 0x0000FF00. What I need as output, however, is either #00ff00 or rgb(0,255,0). How can I achieve that?Solution
Eithers of these 3 should work:
Output:
The
The expression could be used in a computed column definition, or inline table-valued function.
SELECT color
, HEX_1 = '#'
+ CONVERT(varchar(6),
CAST(ABS(color) as varbinary(1))
+ CAST(ABS(color/256) as varbinary(1))
+ CAST(ABS(color/256/256) as varbinary(1))
, 2)
, HEX_2 = '#'+
+CONVERT(varchar(2), CAST(ABS(color) as varbinary(1)), 2)
+CONVERT(varchar(2), CAST(ABS(color/256) as varbinary(1)), 2)
+CONVERT(varchar(2), CAST(ABS(color/256/256) as varbinary(1)), 2)
, RGB = 'rgb('
+ CAST(ABS(color)%256 as varchar(3)) + ','
+ CAST(ABS(color/256)%256 as varchar(3)) + ','
+ CAST(ABS(color/256/256)%256 as varchar(3)) + ')'
FROM (
values
(-65280)
, (-65535)
, (-460293)
, (-13606962)
, (-3678732)
) as colors(color)Output:
color | HEX_1 | HEX_2 | RGB
-65280 | #00FF00 | #00FF00 | rgb(0,255,0)
-65535 | #FFFF00 | #FFFF00 | rgb(255,255,0)
-460293 | #050607 | #050607 | rgb(5,6,7)
-13606962 | #32A0CF | #32A0CF | rgb(50,160,207)
-3678732 | #0C2238 | #0C2238 | rgb(12,34,56)The
CONVERT with style 2 requires SQL Server 2008 or later.The expression could be used in a computed column definition, or inline table-valued function.
Code Snippets
SELECT color
, HEX_1 = '#'
+ CONVERT(varchar(6),
CAST(ABS(color) as varbinary(1))
+ CAST(ABS(color/256) as varbinary(1))
+ CAST(ABS(color/256/256) as varbinary(1))
, 2)
, HEX_2 = '#'+
+CONVERT(varchar(2), CAST(ABS(color) as varbinary(1)), 2)
+CONVERT(varchar(2), CAST(ABS(color/256) as varbinary(1)), 2)
+CONVERT(varchar(2), CAST(ABS(color/256/256) as varbinary(1)), 2)
, RGB = 'rgb('
+ CAST(ABS(color)%256 as varchar(3)) + ','
+ CAST(ABS(color/256)%256 as varchar(3)) + ','
+ CAST(ABS(color/256/256)%256 as varchar(3)) + ')'
FROM (
values
(-65280)
, (-65535)
, (-460293)
, (-13606962)
, (-3678732)
) as colors(color)color | HEX_1 | HEX_2 | RGB
-65280 | #00FF00 | #00FF00 | rgb(0,255,0)
-65535 | #FFFF00 | #FFFF00 | rgb(255,255,0)
-460293 | #050607 | #050607 | rgb(5,6,7)
-13606962 | #32A0CF | #32A0CF | rgb(50,160,207)
-3678732 | #0C2238 | #0C2238 | rgb(12,34,56)Context
StackExchange Database Administrators Q#127448, answer score: 10
Revisions (0)
No revisions yet.