In this post, I will show you how to Convert String To Color in SQL Server. The following code will describe how to convert String To Color In SQL Server 2016. And now write code below:
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
HostForLIFE.eu SQL 2016 Hosting
European best, cheap and reliable ASP.NET hosting with instant activation. HostForLIFE.eu is #1 Recommended Windows and ASP.NET hosting in European Continent. With 99.99% Uptime Guaranteed of Relibility, Stability and Performace. HostForLIFE.eu security team is constantly monitoring the entire network for unusual behaviour. We deliver hosting solution including Shared hosting, Cloud hosting, Reseller hosting, Dedicated Servers, and IT as Service for companies of all size.
