0

Currently I remove all non numeric characters and spacing using the following nested REPLACE function. This enables me to compare a telephone number on the database without worrying if the telephone number has been entered containing non numeric values

SQL Example:

SELECT * FROM myTable
WHERE replace( replace( replace( replace( replace( TelephoneCol, ' ', '' ), '-', '' ), '(', '' ), ')', '' ), '+', '') = '01254566876'

The above query works perfectly and will find a match if there's a telephone number in the database which is formatted like (0) 1254566876. The problem is that it's very slow. Is there a REGEX or a better way of selecting a value from the database with all non numeric characters removed?

I'm using a custom PDO function that I parse an sql string into. I am not using deprecated mysql functions.

steve
  • 471
  • 6
  • 15
  • Why should that be "very slow"? And why don't you save the telephone number in normalized format in the database such that you do all that replacing once (on updating the row) and read the data without doing such hocuspocus? – Nico Haase Apr 16 '18 at 18:54
  • 1
    String operations in mysql are very slow, especially when you do 5 in a row... Couldn't a LIKE possibly help? Or can't you save them better beforehand? – Jeff Apr 16 '18 at 18:54
  • how many rows you have in your table ? and how many second is the duration for your query? – ScaisEdge Apr 16 '18 at 18:55
  • 1
    what about create this function? https://stackoverflow.com/questions/287105/mysql-strip-non-numeric-characters-to-compare – Juan Carlos Oropeza Apr 16 '18 at 18:56
  • Well is slow because you can't use index – Juan Carlos Oropeza Apr 16 '18 at 18:56

0 Answers0