Regex: Ignore Characters in String Comparison

Question

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.

Possible duplicate of How do you extract a numerical value from a string in a MySQL query? — Bobot
– Bobot, Commented Nov 29, 2016 at 16:02
stackoverflow.com/questions/6604455/… , sorry if misunderstood. — Progrock
– Progrock, Commented Nov 29, 2016 at 16:08
Thanks @Progrock - unfortunately that links to a preg_replace() which is PHP when, in the end, I'll need this to be MySQL — Verl
– Verl, Commented Nov 29, 2016 at 16:22
@Bobot - Thanks, it looks like that would need to go through a function. I take it there's no way to do this as a single Regex? — Verl
– Verl, Commented Nov 29, 2016 at 16:22
While there is no preg_replace_callback in sql ... btw you can maybe do something by building a regex like <firstDigit>%<secondDigit>%... — Bobot
– Bobot, Commented Nov 29, 2016 at 16:39

Rick James · Accepted Answer · 2016-11-29 21:58:48Z

1

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.

answered Nov 29, 2016 at 21:58

Rick James

144k15 gold badges144 silver badges254 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Verl Over a year ago

Thanks Rick James - I think this is the best solution available, other than simply biting the bullet and changing the column type to an alternate format so that I could run a CAST() or CONVERT() function on it (if I'm understanding those functions correctly). Appreciate the help!

Rick James Over a year ago

I don't see how CAST or CONVERT would help, unless all the digits are at the beginning.

Verl Over a year ago

Rick James - You're right, I did some more testing and cast/convert just seems to cut off all the text after the first non-numeric character. So truly converting it to an INT(x) column looks like it'd be the solution, assuming we wanted to go through the work of cleansing the current data. Do you see it the same way?

Rick James Over a year ago

I believe in cleansing data before inserting into the database. In rare cases, I would keep the original string in one column and the searchable (just digits) string in another column. (Whether the cleansed column is INT, INT UNSIGNED, BIGINT, VARCHAR(22) CHARACTER SET ascii, etc, is a minor concern.)

Collectives™ on Stack Overflow

Regex: Ignore Characters in String Comparison

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related