CODEWITHSUNDEEP
phpmysqlregex
Verl
Verl

Reputation: 143

Regex: Ignore Characters in String Comparison

I'm trying to find a regular expression to replace this idea:

if($input === numbers_only($phone_number)) {
    // do something
}

Assumptions

$input will always be numeric

numbers_only() strips all non-numeric characters

Context

At the end of the day I need to write a MySQL query that does this against a column formatted as TEXT which is why I can't simply use a function like the above. So it'd look something like:

SELECT 
    number
FROM
    phones
WHERE
    number REGEXP '...'

Is this something regex can handle?

Update

I think a better way to phrase this question (or another way to come at it) may be if there's a way the CONVERT() method in MySQL can handle converting TEXT (not varchar) to INT for a comparison.

Upvotes: 2

Views: 284

Answers (1)

Rick James
Rick James

Reputation: 142238

It would be better to cleanse the data before storing in the table. This way, you have the full power of the client language where you could do something like preg_replace('/[^0-9]+/', '', $num) (if PHP). Perhaps you should go the effort now to fix all the data?

But, assuming that you are stuck with garbage in the column, let's see what we can do.

It seems that you are talking about phone numbers? There are only a small number of characters (perhaps "-() ") that are likely to exist? So...

WHERE $num = REPLACE(...(REPLACE(number, '-', ''), '(', '')...)

Really messy. And really inefficient.

Upvotes: 1

Related Questions