Filter numeric column containing a digit

November 23, 2022

Is there any way to filter rows of a table where a numeric column contains a digit using maths?

I mean, currently, I’m solving that using:

where cast(t.numeric_column as varchar(255)) like "%2%"

However, I would like to know if could be possible to filter apply numeric operations…

Any ideas?

>Solution :

You could use division plus the modulus, if you knew the range of possible numbers. For example, assuming all expected numbers were positive and less than 100,000, you could use:

SELECT *
FROM yourTable
WHERE numeric_column % 10 = 2 OR
      (numeric_column / 10) % 10 = 2 OR
      (numeric_column / 100) % 10 = 2 OR
      (numeric_column / 1000) % 10 = 2 OR
      (numeric_column / 10000) % 10 = 2;

Although the above is ugly and unwieldy, it might actually outperform your approach which requires a costly conversion to string.