MS SQL: Store a Color (ARGB) as an Integer in Transact-SQL

Microsoft SQL ServerLeave a Comment on MS SQL: Store a Color (ARGB) as an Integer in Transact-SQL

MS SQL: Store a Color (ARGB) as an Integer in Transact-SQL

The most common way is to store colors in a SQL database as a string, i.e. the HTML color code (ex. #FFFFFF). The colors can also be stored as 32 bit integers. A 32 bit color value (including alpha channel) can be calculated in the following way:

@Alpha * 16777216 + @Red * 65536 + @Green * 256 + @Blue

The complete user-defined function is as follows:

-- Description: Generate ARGB Color value
-- =============================================
CREATE FUNCTION [dbo].[sudf_Color_FromArgb]
(
 @intAlpha tinyint,
 @intRed  tinyint,
 @intGreen tinyint,
 @intBlue tinyint
)
RETURNS int
AS
BEGIN

    declare @lngColor bigint;
    declare @intColor int;

    -- Generate the color
    set @lngColor = (
        (cast(@intAlpha as bigint) * 16777216) +
        (cast(@intRed as bigint) * 65536) +
        (cast(@intGreen as bigint) * 256)  +
        (cast(@intBlue as bigint))
    );

    -- Get the color
    set @intColor = cast(cast(@lngColor as binary(4)) as int);

    -- Return the color
    return @intColor;
END
Ulf Emsoy has long working experience in project management, software development and supply chain management.

Leave a Reply

Your email address will not be published. Required fields are marked *

Back To Top