CODEWITHSUNDEEP
sqlsql-server
Jordi
Jordi

Reputation: 23247

Filter numeric column containing a digit

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?

Upvotes: 0

Views: 134

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521409

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.

Upvotes: 1

Related Questions