Those who've stumbled upon this post are probably looking for a way to perform a LIKE statement on a numeric value column in a MySQL, MariaDB or SQL Server Database. This usually happens when we need to restrict our results to those who match the first N starting numbers in that column.
When dealing with string values, we could use the LIKE syntax in the following way (assuming the starting numbers are 123):
|
1 |
SELECT * FROM MyTable WHERE MyColumn LIKE '123%' |
Unfortunately, the LIKE operator can't be used if the column hosts numeric types.
Luckily enough, there are at least two alternatives we can use instead.
Solution #1: CAST and LIKE
If we must use the LIKE operator, for example because we want to also get the values that contain (and/or end with) our numbers, we can do the following:
|
1 |
SELECT * FROM MyTable WHERE CAST(MyColumn as CHAR) LIKE '123%' |
However, doing that will force the underlying DB engine to perform that cast on all your rows - which can be quite inefficient, expecially when dealing with a lot of rows.
Solution #2: Divide by POWER
If you only need to match the starting numbers of your column, you can use the following solution instead:
|
1 |
SELECT * FROM MyTable WHERE (LEN(MyColumn) >= LEN(123) AND MyColumn / POWER(10, LEN(MyColumn) - LEN(123)) = 123) |
As we can see, the numbers that we want to match are used twice: first to calculate the POWER parameter to perform an integer division, then to the right part of the resulting equivalence to match the division's result with the numbers we're looking for.
The above method has many performance advantages over the previous one, thus being way faster: the only real limitation is that we cannot use that to find all the column occurrences that Contain those number we're looking for: if we really need that, we'll have to use the previous method instead.
One Comment on “LIKE operator equivalent for integer / numeric value Columns in a SQL (or T-SQL) Database”