在mysql中，如何选择字段的数字部分？

Question

I have a table containing free-form phone numbers (e.g. '123-456-7890', '[phone]', or '123.456.7890') and I'm searching them with a numeric string- e.g., '123456'.

Right now, I'm having to do a bunch of replace(replace(replace(.. ad nauseum functions to strip out errant non-numeric characters then use that 'LIKE' the search value to search.

Anyone know of a simpler way to select only certain characters from a field based on type, not position?

Answer 1

You can use regex in MySQL -

SELECT * 
FROM `table` 
WHERE `phone` REGEXP '^[12345]'

Here is a working example.