CODEWITHSUNDEEP
mysqlregexreplace
candies
candies

Reputation: 131

How to clean up the punctuations, spaces, and letter contain using REGEX

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.

Upvotes: 0

Views: 3479

Answers (2)

Mark Finch
Mark Finch

Reputation: 766

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?

Upvotes: 1

Borodin
Borodin

Reputation: 126722

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.

Upvotes: 1

Related Questions