6

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?

  • 4
    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. – Daniel Hutmacher Jan 27 '16 at 9:07
10

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.

| improve this answer | |
  • 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 '19 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. – Julien Vavasseur Mar 6 '19 at 22:34
0

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)

| improve this answer | |
  • 5
    This may work, but scalar functions (as implemented above) can exhibit extremely poor performance when called many times in a statement. – Paul White Jan 27 '16 at 9:56
0

Getting HTML RGB from INT in one statement:

select '#' +  right(master.sys.fn_varbintohexstr(@c),6)
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.