Title: Doctrine query by phone number in unknown format
Tags: symfony;doctrine
Question: Users store their phone numbers in different formats eg.: +1234567890 +1 (234) 567 890 etc.
I try to get user record from DB by phone number. Looks like I have to use Doctrine beberlei/DoctrineExtensions to make REGEX query but I don't understand how exactly to build query. Code below doesn't works.
$query = $this-&gt;createQueryBuilder('user')
-&gt;where('REGEXP(user.phone, :regexp) = :phone')
-&gt;setParameter('phone', preg_replace("/[^0-9]/", "", $phone ))
-&gt;setParameter('regexp', "[0-9]");
Comment: Hello, I think that you need to add a new field and save the phone number without spaces or signs.

Comment: Can you show sample input and then tell us what should match and what should not?  You might have to use raw SQL here if you want to use `REGEXP`.  Yes, your current code should not run.

Comment: Honestly, I don't really see regex helping much in this example.  You just need to strip of all whitespace, and remove `+` and parentheses, then compare the two strings.

Comment: Either that, or you do it in the query.  Or, you could store the prefix, extension, and number in separate columns.

Comment: I was writing an answer, but then decided not to answer, because the best course of action here is to store the numbers in a clean numeric format, without the extra junk.  Then, assuming you are also comparing against all numbers, you would only need a simple `WHERE` clause.

Comment: Let's say, phone stored as +1 (234) 567 890, query contains phone as 1234567890 have to match stored number.

Comment: I did it with number in query, but how to match it with stored string? Or I have to strip all non-numbers while storing phone number to DB?

Comment: So that is a question - how to make query strip non-numeric chars from stored value to compare it with all numeric string?


Here is another answer: I have done extended work with parsing phonenumbers. When you store a phonenumber, you store it as a text. If you want to find a phonenumber, you use the LIKE clause. Also don't forget to enclose the phonenumber in quotes and %% signs.
Example WHERE clause:
WHERE phonenumber LIKE '%1234567890%'
So clean up the number and then use the above method to search for it.

{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 1, 'CommentCount': 8, 'ContentLicense': 'CC BY-SA 4.0', 'CreationDate': '2018:00:12/26/18', 'FavoriteCount': None, 'PostTypeId': 1, 'Tags': '<symfony><doctrine>', 'Title': 'Doctrine query by phone number in unknown format', 'ViewCount': 278, 'id': 53932730, 'user_AboutMe': '', 'user_AccountId': 6482054.0, 'user_DisplayName': 'Mach', 'user_Reputation': 1.0, 'user_Views': 1.0, 'user_id': 5018748.0}}