代码之家  ›  专栏  ›  技术社区  ›  Scott and the Dev Team

如何使用T-SQL将十六进制颜色转换为和RGB颜色

  •  2
  • Scott and the Dev Team  · 技术社区  · 15 年前

    如何使用T-SQL将十六进制颜色转换为和RGB颜色

    2 回复  |  直到 7 年前
        1
  •  5
  •   Cœur Gustavo Armenta    7 年前

    https://data.stackexchange.com/stackoverflow/query/4803/so3087921

    DECLARE @temp AS TABLE (hex char(6))
    
    INSERT  INTO @temp
    VALUES  ('3333CC') -- Should convert to Red: 51 Green: 51 Blue: 204
    
    DECLARE @table AS varchar(16)
    SET @table = '0123456789abcdef' -- Assuming case-insensitive collation!
    
    SELECT  hex
           ,16 * (CHARINDEX(SUBSTRING(hex, 1, 1), @table) - 1) + (CHARINDEX(SUBSTRING(hex, 2, 1), @table) - 1) AS R
           ,16 * (CHARINDEX(SUBSTRING(hex, 3, 1), @table) - 1) + (CHARINDEX(SUBSTRING(hex, 4, 1), @table) - 1) AS G
           ,16 * (CHARINDEX(SUBSTRING(hex, 5, 1), @table) - 1) + (CHARINDEX(SUBSTRING(hex, 6, 1), @table) - 1) AS B
    FROM    @temp
    

    https://dpatrickcaldwell.blogspot.com/2009/05/converting-hexadecimal-or-binary-to.html

    如果可能的话,我通常会避免使用标量函数,因为可以诱使自己认为调用500万行的标量UDF而不影响内联表达式。

    还可以使用内联表值UDF:

    CREATE FUNCTION dbo.udf_HexToRGB (@hex char(6))
    RETURNS TABLE
    AS RETURN
        (
         SELECT 16 * (CHARINDEX(SUBSTRING(@hex, 1, 1), '0123456789abcdef') - 1) + (CHARINDEX(SUBSTRING(@hex, 2, 1),
                                                                                             '0123456789abcdef') - 1) AS R
               ,16 * (CHARINDEX(SUBSTRING(@hex, 3, 1), '0123456789abcdef') - 1) + (CHARINDEX(SUBSTRING(@hex, 4, 1),
                                                                                             '0123456789abcdef') - 1) AS G
               ,16 * (CHARINDEX(SUBSTRING(@hex, 5, 1), '0123456789abcdef') - 1) + (CHARINDEX(SUBSTRING(@hex, 6, 1),
                                                                                             '0123456789abcdef') - 1) AS B
        )
    GO
    
    DECLARE @temp AS TABLE (hex char(6)) 
    
    INSERT  INTO @temp 
    VALUES  ('3333CC') -- Should convert to Red: 51 Green: 51 Blue: 204 
    
    SELECT  hex 
           ,R 
           ,G 
           ,B 
    FROM    @temp
    OUTER APPLY dbo.udf_HexToRGB(hex)
    
        2
  •  3
  •   Schmalls    15 年前

    这是我刚写的一个表值函数。它将十六进制颜色值作为 char(6) 值并返回带有 R , G B 柱。如果您不在SQL Server上,那么应该很容易适应您的数据库。

    -- =============================================
    -- Author:      Josh Thompson
    -- Create date: 2010-06-21
    -- Description: Convert hex color to RGB
    -- =============================================
    CREATE FUNCTION [fn_hex_to_rgb](
        @hex char(6)
        )
    RETURNS TABLE 
    AS
    RETURN (
        SELECT
            (
                (
                    CASE LOWER(SUBSTRING(@hex, 1, 1))
                        WHEN 'a'
                            THEN 10
                        WHEN 'b'
                            THEN 11
                        WHEN 'c'
                            THEN 12
                        WHEN 'd'
                            THEN 13
                        WHEN 'e'
                            THEN 14
                        WHEN 'f'
                            THEN 15
                            ELSE CAST(SUBSTRING(@hex, 1, 1) AS int)
                        END
                    *
                    16
                    )
                +
                (
                    CASE LOWER(SUBSTRING(@hex, 2, 1))
                        WHEN 'a'
                            THEN 10
                        WHEN 'b'
                            THEN 11
                        WHEN 'c'
                            THEN 12
                        WHEN 'd'
                            THEN 13
                        WHEN 'e'
                            THEN 14
                        WHEN 'f'
                            THEN 15
                            ELSE CAST(SUBSTRING(@hex, 2, 1) AS int)
                        END
                    )
                ) AS R,
            (
                (
                    CASE LOWER(SUBSTRING(@hex, 3, 1))
                        WHEN 'a'
                            THEN 10
                        WHEN 'b'
                            THEN 11
                        WHEN 'c'
                            THEN 12
                        WHEN 'd'
                            THEN 13
                        WHEN 'e'
                            THEN 14
                        WHEN 'f'
                            THEN 15
                            ELSE CAST(SUBSTRING(@hex, 3, 1) AS int)
                        END
                    *
                    16
                    )
                +
                (
                    CASE LOWER(SUBSTRING(@hex, 4, 1))
                        WHEN 'a'
                            THEN 10
                        WHEN 'b'
                            THEN 11
                        WHEN 'c'
                            THEN 12
                        WHEN 'd'
                            THEN 13
                        WHEN 'e'
                            THEN 14
                        WHEN 'f'
                            THEN 15
                            ELSE CAST(SUBSTRING(@hex, 4, 1) AS int)
                        END
                    )
                ) AS G,
            (
                (
                    CASE LOWER(SUBSTRING(@hex, 5, 1))
                        WHEN 'a'
                            THEN 10
                        WHEN 'b'
                            THEN 11
                        WHEN 'c'
                            THEN 12
                        WHEN 'd'
                            THEN 13
                        WHEN 'e'
                            THEN 14
                        WHEN 'f'
                            THEN 15
                            ELSE CAST(SUBSTRING(@hex, 5, 1) AS int)
                        END
                    *
                    16
                    )
                +
                (
                    CASE LOWER(SUBSTRING(@hex, 6, 1))
                        WHEN 'a'
                            THEN 10
                        WHEN 'b'
                            THEN 11
                        WHEN 'c'
                            THEN 12
                        WHEN 'd'
                            THEN 13
                        WHEN 'e'
                            THEN 14
                        WHEN 'f'
                            THEN 15
                            ELSE CAST(SUBSTRING(@hex, 6, 1) AS int)
                        END
                    )
                ) AS B
        )
    GO