Convert int to RGB or hex color

Question

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?

Answer 1

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.

Answer 2

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)

Answer 3

Getting HTML RGB from INT in one statement:

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