Check for an Empty Table

The winners for the January Reader Challenge, "Converting a Hexadecimal String to a Varbinary Value" will be announced at a later date. Please watch this article page for updates. Here's a recap of the problem and the solution to the January Reader Challenge.

Problem:

Bart is a DBA who maintains several data warehouses, which are hosted on SQL Server 2000. One data warehouse has a table called ExtFeeds that periodically receives data as flat files loaded from an external source. One of the columns in the flat file has a hexadecimal string value, ranging from 4 to 32 bytes, which is stored in the ExtFeeds table’s DataHdr column. The external source can't be modified easily, and other applications rely on the DataHdr values to be strings, so Bart can't manipulate the value or convert to binary data. Instead he loads the files directly into the table by using the bcp utility.

However, Bart needs to periodically write queries against the ExtFeeds table that involve accessing the DataHdr column. The queries perform various operations such as converting the hexadecimal string value to varbinary and joining the ExtFeeds table with other tables.Help Bart design a solution that helps convert the hexadecimal strings to varbinary values. He would need to perform the conversion in the SELECT list of queries, in the logic in stored procedures, and in INSERT and UPDATE statements. The solution should be able to support values ranging from 4 to 32 bytes and perform efficiently.

Solution:

Bart needs to write a T-SQL scalar user-defined function (UDF) to convert the hexadecimal string to varbinary because SQL Server doesn’t contain any built-in function that does so. He can’t use the CAST or CONVERT function because doing so converts the string value to a binary value according to the ASCII value of each character in the string. To convert the hexadecimal string value to binary, Bart can use a logic that converts one byte at a time.

Let’s first consider the hexadecimal value “9e” and how it can be converted to binary data. Bart can convert each character in the hexadecimal string to the corresponding numerical value. Next, according to the position of the character in the string of hexadecimal digits, he needs to multiply the numeric value by power of 16. Bart can convert the string to a numeric value by simply looking up the position of the character from a known set of sequential hexadecimal digits by using the following charindex function:

DECLARE @hexdigits CHAR(16), @hexstr VARCHAR(2) 
SET @hexdigits = '0123456789abcdef'
SET @hexstr = '9e'
SELECT  charindex(substring(@hexstr, 02, 1), @hexdigits)-1 AS nibble2, 
        (charindex(substring(@hexstr, 01, 1), @hexdigits)-1)*16 AS nibble1

/*
    nibble2     nibble1
----------- -----------
         14         144
*/

Now, Bart can handle situations in which the input value is empty, one character in length, or incorrect. If the charindex function in the text code below returns zero (i.e., the test didn't find the character it was looking for in the @hexdigits string), the SELECT statement returns a NULL value. The modified code is shown below with different input values:

DECLARE @hexdigits CHAR(16), @hexstr VARCHAR(2) 
SET @hexdigits = '0123456789abcdef'
SET @hexstr = 'f'
SELECT  NULLIF(charindex(substring(@hexstr, 02, 1), @hexdigits), 0)-1 AS nibble2, 
       (NULLIF(charindex(substring(@hexstr, 01, 1), @hexdigits), 0)-1)*16 AS nibble1
/*
    nibble2     nibble1
----------- -----------
       NULL         240
*/

SET @hexstr = '-k'
SELECT  NULLIF(charindex(substring(@hexstr, 02, 1), @hexdigits), 0)-1 AS nibble2, 
       (NULLIF(charindex(substring(@hexstr, 01, 1), @hexdigits), 0)-1)*16 AS nibble1
/*
    nibble2     nibble1
----------- -----------
       NULL        NULL
*/

Bart can easily convert to binary data by casting the numeric value to binary(1). He can then use COALESCE to return the binary value if it is non-null and empty binary data otherwise using the following code.

DECLARE @hexdigits CHAR(16), @hexstr VARCHAR(2) 
SET @hexdigits = '0123456789abcdef'
SET @hexstr = '9e'
SELECT  COALESCE(CAST(NULLIF(charindex(substring(@hexstr, 02, 1), @hexdigits),0)-1 +
                     (NULLIF(charindex(substring(@hexstr, 01, 1), @hexdigits), 0)-1)*16 AS binary(1)), 
0x) AS byte1

Finally, Bart can incorporate this logic into a scalar UDF that uses the conversion expression above for longer length strings. An input of up to 32 characters can be converted to varbinary(32) values, as the following scalar UDF shows:

CREATE FUNCTION hexstr2bin (@hexstr VARCHAR(32)) 
RETURNS varbinary(32) 
WITH schemabinding
AS
BEGIN
 DECLARE @hexdigits char(16) 
 SET @hexdigits = '0123456789abcdef'
 /* Ensure that the input is in lower-case for case-insensitive lookups. */
 SET @hexstr = lower(@hexstr) 
 RETURN
    COALESCE(CAST((NULLIF(charindex(substring(@hexstr, 02, 1), @hexdigits), 0)-1) 
               + ((NULLIF(charindex(substring(@hexstr, 01, 1), @hexdigits), 0)-1)*16) 
  AS binary(1)), 0x) +
    COALESCE(CAST((NULLIF(charindex(substring(@hexstr, 04, 1), @hexdigits), 0)-1) 
               + ((NULLIF(charindex(substring(@hexstr, 03, 1), @hexdigits), 0)-1)*16) 
  AS binary(1)), 0x) +
    COALESCE(CAST((NULLIF(charindex(substring(@hexstr, 06, 1), @hexdigits), 0)-1) 
               + ((NULLIF(charindex(substring(@hexstr, 05, 1), @hexdigits), 0)-1)*16) 
  AS binary(1)), 0x) +
    COALESCE(CAST((NULLIF(charindex(substring(@hexstr, 08, 1), @hexdigits), 0)-1) 
               + ((NULLIF(charindex(substring(@hexstr, 07, 1), @hexdigits), 0)-1)*16) 
  AS binary(1)), 0x) +
    COALESCE(CAST((NULLIF(charindex(substring(@hexstr, 10, 1), @hexdigits), 0)-1) 
               + ((NULLIF(charindex(substring(@hexstr, 09, 1), @hexdigits), 0)-1)*16) 
  AS binary(1)), 0x) +
    COALESCE(CAST((NULLIF(charindex(substring(@hexstr, 12, 1), @hexdigits), 0)-1) 
               + ((NULLIF(charindex(substring(@hexstr, 11, 1), @hexdigits), 0)-1)*16) 
  AS binary(1)), 0x) +
    COALESCE(CAST((nullif(charindex(substring(@hexstr, 14, 1), @hexdigits), 0)-1) 
               + ((NULLIF(charindex(substring(@hexstr, 13, 1), @hexdigits), 0)-1)*16) 
  AS binary(1)), 0x) +
    COALESCE(CAST((NULLIF(charindex(substring(@hexstr, 16, 1), @hexdigits), 0)-1) 
               + ((NULLIF(charindex(substring(@hexstr, 15, 1), @hexdigits), 0)-1)*16) 
  AS binary(1)), 0x) +
    COALESCE(CAST((NULLIF(charindex(substring(@hexstr, 18, 1), @hexdigits), 0)-1) 
               + ((NULLIF(charindex(substring(@hexstr, 17, 1), @hexdigits), 0)-1)*16) 
   AS binary(1)), 0x) +
    COALESCE(CAST((NULLIF(charindex(substring(@hexstr, 20, 1), @hexdigits), 0)-1) 
               + ((NULLIF(charindex(substring(@hexstr, 19, 1), @hexdigits), 0)-1)*16) 
  AS binary(1)), 0x) +
    COALESCE(CAST((NULLIF(charindex(substring(@hexstr, 22, 1), @hexdigits), 0)-1) 
               + ((NULLIF(charindex(substring(@hexstr, 21, 1), @hexdigits), 0)-1)*16) 
   AS binary(1)), 0x) +
    COALESCE(CAST((NULLIF(charindex(substring(@hexstr, 24, 1), @hexdigits), 0)-1) 
               + ((NULLIF(charindex(substring(@hexstr, 23, 1), @hexdigits), 0)-1)*16) 
  AS binary(1)), 0x) +
    COALESCE(CAST((NULLIF(charindex(substring(@hexstr, 26, 1), @hexdigits), 0)-1) 
               + ((NULLIF(charindex(substring(@hexstr, 25, 1), @hexdigits), 0)-1)*16) 
  AS binary(1)), 0x) +
    COALESCE(CAST((NULLIF(charindex(substring(@hexstr, 28, 1), @hexdigits), 0)-1) 
               + ((NULLIF(charindex(substring(@hexstr, 27, 1), @hexdigits), 0)-1)*16) 
  AS binary(1)), 0x) +
    COALESCE(CAST((NULLIF(charindex(substring(@hexstr, 30, 1), @hexdigits), 0)-1) 
               + ((NULLIF(charindex(substring(@hexstr, 29, 1), @hexdigits), 0)-1)*16) 
  AS binary(1)), 0x) +
    COALESCE(CAST((NULLIF(charindex(substring(@hexstr, 32, 1), @hexdigits), 0)-1) 
               + ((NULLIF(charindex(substring(@hexstr, 31, 1), @hexdigits), 0)-1)*16) 
  AS binary(1)), 0x) 
END
GO

-- Sample queries: 

SELECT dbo.hexstr2bin('D1B60872') AS hexval
SELECT dbo.hexstr2bin('FDED') AS hexval
SELECT dbo.hexstr2bin('74184da85c668a74acbb') AS hexval
GO

SELECT TOP 25 newid() AS i
INTO #t
FROM dbo.sysobjects
SELECT t.i, dbo.hexstr2bin(REPLACE(CAST(t.i as VARCHAR(80)), '-', ')) AS hexval
FROM #t AS t


DROP TABLE #t
GO

FEBRUARY READER CHALLENGE:

Now, test your SQL Server savvy in the February Reader Challenge, "Check for an Empty Table" (below). Submit your solution in an email message to [email protected] by February 1. Umachandar Jayachandran, a SQL Server Magazine technical editor, will evaluate the responses. We’ll announce the winner in an upcoming SQL Server Magazine UPDATE. The first-place winner will receive $100, and the second-place winner will receive $50.

Problem:

Steve is a DBA who manages several SQL Server 2000 installations in his company. He needs to create a batch job that exports and imports several tables from the database servers. As part of the batch job, Steve would like to invoke a stored procedure that can determine whether a table is empty. According to the stored procedure result, he will then perform appropriate actions like executing DBCC SHOWCONTIG or DBCC DBREINDEX statements in his batch job.

Help Steve design a stored procedure that does the following:1. Determines whether a table in a particular database is empty. (The tables can be present in different user schemas.)
2. Decides the appropriate parameters for the stored procedure and table name as a mandatory parameter.
3. Returns the status of the table (empty or not empty) as an output parameter.