CREATE FUNCTION dbo.fn_conversion_string_color
(
@in_string VARCHAR(200)
)
RETURNS NVARCHAR(500)
AS
BEGIN
DECLARE @fsetprefix BIT, -- append '0x' to the output
@pbinin VARBINARY(MAX), -- input binary stream
@startoffset INT, -- starting offset
@cbytesin INT, -- length of input to consider, 0 means total length
@pstrout NVARCHAR(MAX),
@i INT,
@firstnibble INT ,
@secondnibble INT,
@tempint INT,
@hexstring CHAR(16)
SELECT @fsetprefix = 1,
@pbinin = SUBSTRING(HASHBYTES('SHA1', @in_string), 1, 3),
@startoffset = 1,
@cbytesin = 0
-- initialize and validate
IF (@pbinin IS NOT NULL)
BEGIN
SELECT @i = 0,
@cbytesin = CASE WHEN (@cbytesin > 0 AND @cbytesin <= DATALENGTH(@pbinin))
THEN @cbytesin
ELSE DATALENGTH(@pbinin)
END,
@pstrout = CASE WHEN (@fsetprefix = 1)
THEN N'0x'
ELSE N''
END,
@hexstring = '0123456789abcdef'
--the output limit for nvarchar(max) is 2147483648 (2^31) bytes, that is 1073741824 (2^30) unicode characters
IF (
((@cbytesin * 2) + 2 > 1073741824)
OR ((@cbytesin * 2) + 2 < 1)
OR (@cbytesin IS NULL )
)
RETURN NULL
IF (
( @startoffset > DATALENGTH(@pbinin) )
OR (@startoffset < 1 )
OR (@startoffset IS NULL )
)
RETURN NULL
-- adjust the length to process based on start offset and total length
IF ((DATALENGTH(@pbinin) - @startoffset + 1) < @cbytesin)
SELECT @cbytesin = DATALENGTH(@pbinin) - @startoffset + 1
-- do for each byte
WHILE (@i < @cbytesin)
BEGIN
-- Each byte has two nibbles which we convert to character
SELECT @tempint = CAST(SUBSTRING(@pbinin, @i + @startoffset, 1) AS INT)
SELECT @firstnibble = @tempint / 16
SELECT @secondnibble = @tempint % 16
-- we need to do an explicit cast with substring for proper string conversion.
SELECT @pstrout = @pstrout +
CAST(SUBSTRING(@hexstring, (@firstnibble+1), 1) AS NVARCHAR) +
CAST(SUBSTRING(@hexstring, (@secondnibble+1), 1) AS NVARCHAR)
SELECT @i = @i + 1
END
END
RETURN '#' + UPPER(RIGHT(@pstrout, 6))
END
