MySQL Regexp在哪里价值[重复]

Question

This question already has an answer here:

• MySQL strip non-numeric characters to compare 14 answers

I am trying to match a numeric value that is purely numbers to another value in my database that is the same numbers but has symbols etc (it's phone numbers).

How can I get MySQL to only match the regexp of the mainNumber column (which has symbols etc in it) to my value (just numbers)?

Below is my code:

$SQL = "SELECT entities.name
        FROM entities_main_numbers
           LEFT JOIN entities ON entities.id = entities_main_numbers.entityId
        WHERE mainNumber = ?
        LIMIT 1";

---

E.g. [phone] to match against 440800123123

</div>

Answer 1

You can be creative and match the 9 digits from the right after getting rid of the spaces, but if the full number is needed you can place few more recursive calls to REPLACE eliminating the () and +

mainnumber = RIGHT(REPLACE('[phone]', ' ', ''), 9);