0

MySQL column:

phone = '(999)-666-333'

String to search '999666333'

Is there a way I could search by transforming the value '(999)-666-333' within WHERE command? I want to strip all non-digits and leave only digits.

something like:

select * from users where regex(phone, '[0-9]') = '999666333';
dtmiRRor
  • 581
  • 4
  • 17
  • 3
    You can, but then mysql will not be able to use indexes to look up a value. You better store the phone numbers the way your are going to search on them and either format them for display purposes or store the display value in a separate field. – Shadow Nov 23 '20 at 09:52
  • I know. unfortunately I already have them stored like that in production (lots of data) and some searches don't work... of course they don't. I'm trying to find a solution how to fix this. – dtmiRRor Nov 23 '20 at 09:54
  • 1
    Add a column which will store the number cleared from all non-digit chars (INT or BIGINT datatype recommended depends on max number length). Update it. Create the index by it and use in your queries. Create triggers which will create/renew this column values during insert/update. – Akina Nov 23 '20 at 10:00
  • @SlavaRozhnev that's a good lead, thank you. Dunno why I didn't think of this :) – dtmiRRor Nov 23 '20 at 10:06
  • @Akina - I reopened this question so you could present your Comment as an Answer. The "dup" (https://stackoverflow.com/questions/287105/mysql-strip-non-numeric-characters-to-compare) specifically says "speed is not an issue". Furthermore, your comment about using a Trigger is an interesting addition for anyone who does not want the cleanse the data first. – Rick James Nov 23 '20 at 16:23

1 Answers1

0

Something like this works in my case:

SELECT * FROM users WHERE REGEXP_REPLACE(phone, '[^0-9]', '') = '999666333';
dtmiRRor
  • 581
  • 4
  • 17