How to Search SQL Server Table for a Hexadecimal Range

sql serversql-server-2016

I have a table of hexadecimal values stored in a varchar(32) column. The values can be anywhere between 000FFF100000 and 000FFFA90FFF

I would like to find all rows between 000FFF300000 and 000FFF7FFF05.

Any suggestions?

Best Answer

Assuming that you can't do the sensible thing and change the datatype of the column to be VARBINARY(10), then just convert the value in the VARCHAR column into VARBINARY(10) and then test to see if it is in that range. For example:

SELECT tmp.*
FROM   (VALUES ('000FFF5060B0'), ('000FFFD00DEE')) tmp(col)
WHERE  CONVERT(VARBINARY(10), tmp.[col], 2) BETWEEN 0x000FFF300000 AND 0x000FFF7FFF05;

returns only the following row:

000FFF5060B0

The style number of "2" in the CONVERT function allows a string of hex bytes without the preceding "0x" to be converted into binary without assuming that the hex bytes are encoded characters.

IF you will be doing such queries frequently, then it would be best to do the following:

  1. Create a non-persisted Computed Column for that expression (i.e. ALTER TABLE {schema_name}.{table_name} ADD {column_name} AS (CONVERT(VARBINARY(10), [{varchar_column_name}], 2)); )
  2. Create a non-clustered Index on that non-persisted computed column
Related Question