LIKE operator on timestamp column, SQL Server

Question

How can I use LIKE operator on a column having timestamp (rowversion) data type ?

Below are the queries I tried but no luck.

-- no data is fetched using this SQL
SELECT * FROM TAB
WHERE tRowVersion LIKE '0x000000000000E05%'

-- incorrect syntax near %
SELECT * FROM TAB
WHERE tRowVersion LIKE 0x000000000000E05%

Also, I cannot use the single quotes with the timestamp values.

-- implicit conversion from data type varchar to timestamp is not allowed
SELECT * FROM TAB
WHERE tRowVersion = '0x000000000000E05'

I would like to find the patterns of row versioning using LIKE operator. Using CAST I am not able to find the patterns.

Any idea ?

Thanks,

Answer 1

Rowversion (of which timestamp is a deprecated synonym) is a binary datatype. It has nothing to do with datetimes. As such CASTing the hexidecimal string representation of the values to datetime/string in order to perform a LIKE will not work. You need to calculate instead which binary values will have hexidecimal string representations starting with the input string.

Assuming a length of 18 for the full rowversion hex string then you could pad out the rest of your input string with 0s to get the minimum value the LIKE should return (were it to work with hex strings - which it doesn't). The maximum value would then either be a pad with Fs or a case of adding (16 to the power of the amount of characters being filtered by the LIKE - 1):

DECLARE @string_to_like varchar(18) = '0x000000000000E05'

DECLARE @chars_to_like int
SELECT @chars_to_like = 18 - len(@string_to_like)

DECLARE @min_rowversion_s varchar(18)
SET @min_rowversion_s =  left(ltrim(@string_to_like) + '00000000000000000', 18)

DECLARE @min_rowversion varbinary(8)
SET @min_rowversion = CONVERT(varbinary(8), @min_rowversion_s, 1) 

DECLARE @max_value  INT
DECLARE @divider int

SET @divider = POWER(16, @chars_to_like)

SELECT @max_value = @min_rowversion + @divider - 1

SELECT * FROM TAB WHERE tRowVersion BETWEEN @min_rowversion AND @max_value

This would get you the results you need i.e. for 0x000000000000E05 everything between 0x000000000000E050 and 0x000000000000E05F, for 0x000000000000E everything between 0x000000000000E000 and 0x000000000000EFFF etc.

You can then wrap the logic in a UDF as necessary.