Peter DeBetta's blog about programming in SQL Server 2008, 2005, etc. using technologies such as T-SQL, .NET, CLR, C#, VB, Visual Studio, and SQL Server Management Studio.
[EDIT] I made a few changes to account for the issues Chris and Cooter noted in the comments.
One last post before I go on vacation next week...
SQL Server 2005 includes an undocumented function, sys.fn_varbintohexstr, that converts a hex value to a string representation of that hex value (0x3a becomes '0x3a'), but there is no function to go back from a hex string to a hex value. Now there is J:
CREATE FUNCTION dbo.HexStrToVarBin(@hexstr VARCHAR(8000))
RETURNS varbinary(8000)
AS
BEGIN
DECLARE @hex CHAR(2), @i INT, @count INT, @b varbinary(8000), @odd BIT, @start bit
SET @count = LEN(@hexstr)
SET @start = 1
SET @b = CAST('' AS varbinary(1))
IF SUBSTRING(@hexstr, 1, 2) = '0x'
SET @i = 3
ELSE
SET @i = 1
SET @odd = CAST(LEN(SUBSTRING(@hexstr, @i, LEN(@hexstr))) % 2 AS BIT)
WHILE (@i <= @count)
BEGIN
IF @start = 1 AND @odd = 1
BEGIN
SET @hex = '0' + SUBSTRING(@hexstr, @i, 1)
END
ELSE
BEGIN
SET @hex = SUBSTRING(@hexstr, @i, 2)
END
SET @b = @b +
CAST(CASE WHEN SUBSTRING(@hex, 1, 1) LIKE '[0-9]'
THEN CAST(SUBSTRING(@hex, 1, 1) AS INT)
ELSE CAST(ASCII(UPPER(SUBSTRING(@hex, 1, 1)))-55 AS INT)
END * 16 +
CASE WHEN SUBSTRING(@hex, 2, 1) LIKE '[0-9]'
THEN CAST(SUBSTRING(@hex, 2, 1) AS INT)
ELSE CAST(ASCII(UPPER(SUBSTRING(@hex, 2, 1)))-55 AS INT)
END AS binary(1))
SET @i = @i + (2 - (CAST(@start AS INT) * CAST(@odd AS INT)))
IF @start = 1
BEGIN
SET @start = 0
END
END
RETURN @b
END
GO
Comment Notification
If you would like to receive an email when updates are made to this post, please register here
Subscribe to this post's comments using
About Peter DeBetta
Peter DeBetta is an independent consultant specializing in design, development, implementation, and deployment of Microsoft SQL Server, Microsoft SharePoint Server, and .NET solutions. Peter writes courseware, articles, and books – most notably the title Introducing SQL Server 2005 for Developers from Microsoft Press. Peter speaks at conferences around the world, including TechEd, SQL PASS Community Summit, DevTeach, SQL Connections, DevWeek, and VSLive!
Peter is a Microsoft MVP for SQL Server, an MCP, President of the
North Texas SQL Server User Group, and a member of PASS.
When Peter isn’t working, you can find him singing and playing guitar (click
here to hear an original song by Peter), taking pictures, or simply enjoying life with his wife, son, and new daughter.