Filter numeric column containing a digit-CodePudding

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?

CodePudding user response：

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.