Can we use Sql BETWEEN operator with Hexa as Strings?

user_19483562 0
Viewed 22

If I use Sql BETWEEN operator with Hexa numbers as Strings, will i get the same result as converting the hexa in to numeric and then performing between operation.

Will the below two sql's get same results.

  1. Product ID is stored in db as hexa string

    SELECT product_name FROM Products WHERE product_id BETWEEN "24ab" AND "82df" ORDER BY product_id;

  2. Product ID is converted from hexa to decimal (int) in DB.

    SELECT product_name FROM Products WHERE product_id BETWEEN 9387 AND 33503 ORDER BY product_id;

My expectation is above two sql's will output same products.

Example of sql BETWEEN operation on strings can be found here.

hexa(24ab) = decimal(9837)  and  
hexa(82df) = decimal(33503)

Just curious - does string comparison use ascii value of each char to compare. I hope so.

hex mysql sql
1 Answers

You're on the right track, but it's more accurate to say that string comparison compares character-by-character, according to the collation defined for the expression. This allows for characters to compare as the same if it's appropriate, according to national rules for string comparison. Not all the world is ASCII, in other words.

For purposes of hexadecimal strings, these include only characters that are ASCII, so we can simplify and say yes, the strings are compared by their ASCII values.

Where some people find trouble is that the hex strings are of different lengths. For example, is FF great than 24AB?

mysql> select 'ff' > '24ab';
+---------------+
| 'ff' > '24ab' |
+---------------+
|             1 |
+---------------+

To use hex strings in inequality comparisons (including BETWEEN), you should make sure the strings have equal length, and if not, then zero-pad the shorter strings.

mysql> select '00ff' > '24ab';
+-----------------+
| '00ff' > '24ab' |
+-----------------+
|               0 |
+-----------------+
Bill_Karwin_208600
Related