3

Hi I'm facing the following problem:

I have phonedata (bought, imported etc...) which now has different formats like

0123/4567
0123 4567
0123/4567 89
(0)123/4567
+00 123456789

I get a "clean" value like 01234567 from the PBX.

So what I would need is something like this (beware, pseudocode)

SELECT pbx.* FROM pbx WHERE pbx.phone LIKE REPLACE(REPLACE(pbx.mobile,' ', ''), '/', '')...

I know this is saniting hell but since all coworkers enter numbers like they please and the third-party lists also consist of a wild variety of data I can't do sanitizing on the records themselves. I also don't want to eat every possible format getting the values above clean would suffice but I can't wrap my head arround how to query that.

I hope you can help me.

Soundz
  • 1,308
  • 10
  • 17
  • 1
    Do you have to do it in SQL, or can you do it before you insert the phone numbers to the DB? If yes in what language? – Gavriel Feb 01 '16 at 13:49
  • You have to control the front end, with some kind of form validation. – Strawberry Feb 01 '16 at 13:50
  • Or you can close this for yourself as a duplicate: http://stackoverflow.com/questions/986826/how-to-do-a-regular-expression-replace-in-mysql if this is a possibility – Gavriel Feb 01 '16 at 13:51
  • @Gavriel No, we get a csv with like 10000 numbers in every kind of format. Since the numberfield is a string the db doesn't care for the format. Also my coworkers enter (for example) foreign numbers on personal preference which leads up to entries like `+00`, `000 123` or even `+00 (0) 123` . I could retrieve all entries and do some cleanup with php and `str_replace` yet I thought there would be a more elegant way to accomplish this – Soundz Feb 01 '16 at 13:59

1 Answers1

2

If you don't care the number format when you retrieve them, then insert the numbers after you cleaned every non-digit from it.

If you do care, then add another field to your table: cleared_phone, and insert the original number to phone and the cleared number to cleared_phone, and when you search use cleared_phone but display phone

Gavriel
  • 18,880
  • 12
  • 68
  • 105
  • Agree with Gavriel to keep the original data format that you have received and create another column that you can put your cleaned up data into. This helps to keep track of the source. You may just run into issues down the road where you realized you did something incorrect and need to reprocess the original values. – edjm Feb 01 '16 at 14:17
  • I guess that's the pill I got to swallow. Thanks for your time – Soundz Feb 01 '16 at 14:19