TECHNOLOGIES
FORUMS
JOBS
BOOKS
EVENTS
INTERVIEWS
Live
MORE
LEARN
Training
CAREER
MEMBERS
VIDEOS
NEWS
BLOGS
Sign Up
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
C# Corner
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
Convert String To Color in SQL Server
sudipta sanyal
Nov 15
2014
Code
2.8
k
0
0
facebook
twitter
linkedIn
Reddit
WhatsApp
Email
Bookmark
expand
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
Sql Server
String
Convert String