LIKE operator equivalent for integer / numeric value Columns in a SQL (or T-SQL) Database

LIKE operator equivalent for integer / numeric value Columns in a SQL (or T-SQL) Database

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):

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:

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:

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.

 

About Ryan

IT Project Manager, Web Interface Architect and Lead Developer for many high-traffic web sites & services hosted in Italy and Europe. Since 2010 it's also a lead designer for many App and games for Android, iOS and Windows Phone mobile devices for a number of italian companies. Microsoft MVP for Development Technologies since 2018.

View all posts by Ryan

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.