CODEWITHSUNDEEP
mysql
GenesisBits
GenesisBits

Reputation: 346

REPLACE non-numeric values and MATCH in WHERE clause

I have a query like so:

SELECT * FROM `mytable` WHERE `code` LIKE 'PR-SM' AND number = '45'

However in some instances the number can be PR45 which will then fail. I have tried the following:

SELECT * FROM `mytable` WHERE `code` LIKE 'PR-SM' AND CAST(number as unsigned) = '45'

SELECT * FROM `mytable` WHERE `code` LIKE 'PR-SM' AND CONVERT(number as unsigned) = '45'

SELECT * FROM `mytable` WHERE `code` LIKE 'PR-SM' AND number LIKE '%45'

LIKE % will not work as it also picks up 145.

REGEXP_REPLACE also does not work as I am on MySQL 5.7

This is needed for my API, because of my setup I need to be able to do it in the where clause.

Sample Table:

+--------------------------+
| code       | number      |
+--------------------------+
| PR-SM      |  PR45       |
+--------------------------+
| PR-SM      |  PR145      |
+--------------------------+
| XYZ        |  177        |
+--------------------------+
| XYZ        |  81         |
+--------------------------+

Upvotes: 0

Views: 30

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520928

Although you are not using MySQL 8+, which means you won't have access to the newer regex functions, on 5.7 REGEXP should still be available:

SELECT *
FROM yourTable
WHERE code = 'PR-SM' AND number REGEXP '(^|[^0-9])45([^0-9]|$)';

Demo

The regex pattern used here says to match:

(^|[^0-9])  match the start of the input OR a non digit
45          match the number 45
([^0-9]|$)  match the end of the input OR a non digit

Upvotes: 1

Related Questions