# Convert int to RGB or hex color

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 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?

• Actually, why are you doing this in a database? I'm sure there are a bunch of existing functions in most frameworks and languages to support int2hex conversions. Jan 27, 2016 at 9:07

Eithers of these 3 should work:

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.

• Thank you! I was able to do this on my RGB table to get HEX values: SELECT '#' + CONVERT(VARCHAR(6), CAST(RGB.R AS VARBINARY(1)) + CAST(RGB.G AS VARBINARY(1)) + CAST(RGB.B AS VARBINARY(1)), 2) AS [HEX] FROM RGBColorTable RGB.
– SNag
Feb 12, 2019 at 10:47
• Thanks. This is just text conversion/cast. Remember that it can be useful for over things too and have a look at cast and convert docs. Mar 6, 2019 at 22:34

For anybody who may come across this problem, I solved using this function:

-- Description:  Converts ARGB to RGB(RR,GG,BB)
--               e.g. 16744703 returns RGB(255,128,255) or #FF80FF
CREATE FUNCTION [dbo].[ARGB2RGB] (@ARGB AS BIGINT,@ColorType AS VARCHAR(1))
RETURNS VARCHAR(16)
AS
BEGIN
DECLARE @Octet1 TINYINT
DECLARE @Octet2 TINYINT
DECLARE @Octet3 TINYINT
DECLARE @Octet4 TINYINT
DECLARE @RestOfColor BIGINT

SET @Octet1 = abs(@ARGB) / 16777216
SET @RestOfColor = abs(@ARGB) - ( @Octet1 * CAST(16777216 AS BIGINT) )
SET @Octet2 = @RestOfColor / 65536
SET @RestOfColor = @RestOfColor - ( @Octet2 * 65536 )
SET @Octet3 = @RestOfColor / 256
SET @Octet4 = @RestOfColor - ( @Octet3 * 256 )

RETURN
CASE @ColorType
WHEN 'R'
THEN 'RGB(' + CONVERT(VARCHAR, @Octet4) + ','
+ CONVERT(VARCHAR, @Octet3) + ',' + CONVERT(VARCHAR, @Octet2)
+ ')'
WHEN 'H'
THEN '#' + RIGHT(sys.fn_varbintohexstr(@Octet4), 2)
+ RIGHT(sys.fn_varbintohexstr(@Octet3), 2)
+ RIGHT(sys.fn_varbintohexstr(@Octet2), 2)
END
END

From Getting RGB(R,G,B) from ARGB integer (SQL) (Stack Overflow)

• This may work, but scalar functions (as implemented above) can exhibit extremely poor performance when called many times in a statement. Jan 27, 2016 at 9:56

Getting HTML RGB from INT in one statement:

select '#' +  right(master.sys.fn_varbintohexstr(@c),6)