0

I have some examples on phone number, as shown below:

0810-093-485-58
0811.4845.5677
0812 567 6788 2
08138475967079/0813845765998
0814749345875 NEW
64739845985
0815756867967 (G) / 022 845 769
00816956865090
0817637698578

I tried to use replace and regexp, as follows:

select replace (number, '','') as phone_number
from details
where number not regexp '[[:punct:] A-Za-z]' and number not regexp '^ [1-9]'

These syntaxs applied only result:

0817637698578

My point here is I want to clean up those punctuation, spaces, and the letter contained to be:

081009348558
081148455677
08125676788 2
08138475967079
0814749345875
0815756867967
0816956865090
0817637698578

So, the result is not only one, but all the phone numbers, just non-numeric is being cleared. What about the syntax? Please help. Thank you.

candies
  • 131
  • 5
  • 16

2 Answers2

1

Your where clause excludes all numbers that contains alphas, spaces or punctuation, so all that you will see are well-formed numbers anyway. Also replace (number, '','') won't make any changes to the string at all.

There is no nice way to do this without a regular expression replace function, which is available as a user-defined function but not a native one. Look at PREG_REPLACE here.

Borodin
  • 126,100
  • 9
  • 70
  • 144
  • Tried to use PREG_REPLACE in PHP, its cannot be used in MySQL? When I've got the results, how the results of the PREG_REPLACE function that has changed can be inserted into the database? Shall I to use query? – candies May 12 '12 at 03:06
  • If you are going to use PHP to modify the string for you, you need to fetch each record using `SELECT`, remove the punctuation, and then change the record using `UPDATE`. As I said, a `PREG_REPLACE` UDF is available for MySQL [here](http://www.mysqludf.org/lib_mysqludf_preg/). – Borodin May 12 '12 at 14:29
  • I use PHP because doesn't work in MySQL. It says "Function ...preg_replace doesn't exist". So, I have to install the function to make its working, right? I will try. Thanks for the info :). – candies May 13 '12 at 03:59
  • @candies: yes, I'm sorry I could have made it clearer. You have to download and install the package that I linked to to make `PREG_REPLACE` available. You should also look at the link that Mark Finch provided to see if you prefer the `REGEXP_REPLACE` offered there, although the two will be very similar. – Borodin May 13 '12 at 14:22
1

Why not do this in the code posting to the database? regexp are a lot easier in most languages than in SQL, and you have the added benefit of not adding more load on the database server.

That of coarse doesn't help you with this question. Have you thought about using a User Defined Function? For instance this package https://launchpad.net/mysql-udf-regexp?

Mark Finch
  • 766
  • 6
  • 16
  • PREG_REPLACE and REGEXP_REPLACE both are different? – candies May 12 '12 at 03:12
  • I suggest adding the PHP code you are using so we can get a better idea of what your problem is. I suggest validating the phone numbers on input. That will keep your database clean. Then all you would need to do is write a sql function and clean the existing data, or if it is easier write a PHP script to pull the records perform the cleanup with PHP Regular Expressions such as: `$str = preg_replace('[\D]', '', $str);`- will remove anything that isn't a number. NOTE: If your phone numbers need common attributes such as '+' or 'x' as in +86 21 5555 8888 x 2020, that those are stripped as well. – Mark Finch May 12 '12 at 17:43
  • Some links that might help you are: [here](http://aloftcorp.com/wordpress/2011/05/02/handy-dandy-php-script-to-clean-phone-numbers/) or [here](http://plasticbrain.net/resources/php-validate-email-address-and-phone-number/) or [here](http://stackoverflow.com/questions/123559/a-comprehensive-regex-for-phone-number-validation/) – Mark Finch May 12 '12 at 17:47