2

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.

Verl
  • 143
  • 9
  • Possible duplicate of [How do you extract a numerical value from a string in a MySQL query?](http://stackoverflow.com/questions/978147/how-do-you-extract-a-numerical-value-from-a-string-in-a-mysql-query) – Bobot Nov 29 '16 at 16:02
  • http://stackoverflow.com/questions/6604455/php-code-to-remove-everything-but-numbers , sorry if misunderstood. – Progrock Nov 29 '16 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 Nov 29 '16 at 16:22
  • 1
    @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 Nov 29 '16 at 16:22
  • While there is no preg_replace_callback in sql ... btw you can maybe do something by building a regex like `%%...` – Bobot Nov 29 '16 at 16:39
  • I'm not sure you could do that in query. It's widely possible in PHP. If you prefer to do that in PHP, you can have it your way. We're here to help you to get it done. – Wolverine Nov 29 '16 at 16:49

1 Answers1

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.

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • 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! – Verl Nov 30 '16 at 20:48
  • I don't see how `CAST` or `CONVERT` would help, unless _all_ the digits are at the beginning. – Rick James Nov 30 '16 at 22:49
  • 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? – Verl Dec 01 '16 at 10:45
  • 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.) – Rick James Dec 01 '16 at 23:26