MySQL query to find a phone number in a table within multiple columns of the table

Question

I have a debt collection database that has 71 columns in 1 table that could possibly have a phone number in it(primary phone, work phone, cell phone, custom1, custom2, custom3) etc..etc... What I'm trying to accomplish is a C# window form app that searches for a phone number you input into the form and finds a match which will give you the account number of the account upon successfully matching the phone number.

Not all phone numbers have the exact same format ie. (444-444-1111, 4444441111, (444)444-1111) etc....So far I haven't been able to find anything on here as to how I can structure the select statement to find a match. I'm a complete noob at this whole process but from what I've found I could possibly use a CONCAT or REGEXP possibly.

Any examples would be greatly appreciated!

This is just a mock-up of what I'm thinking.

select * 
from dbase 
where 
  primaryphone,
  cellphone, 
  workphone, 
  spouseworkphone, 
  spouseemployerphone, 
  employerphone, 
  custom1, 
  custom2, 
  -- all customs fields from 3 to 57
  custom58, 
  custom59, 
  custom60 = '444-444-1111'

Too broad question. We need a [MCVE](https://stackoverflow.com/help/mcve), samples of input and expected output. Please [edit](https://stackoverflow.com/posts/49455524/edit) your question and add this details — Aleks Andreev, Mar 23 '18 at 17:47
Just posted a possible query but its erroring out because i cant group all of those fields in the where clause. — Timbo Slice, Mar 23 '18 at 17:50
How about normalizing the tables so that all sort of phone numbers are added in a separate table (and normalize the phone numbers to a consistent formant while you are at it). — Salman A, Mar 23 '18 at 17:51
Theres only one table that all of the phone numbers go into currently. — Timbo Slice, Mar 23 '18 at 17:52
"Normalize" means get rid of columns that have a numeric indicator (`custom54, custom55, custom56`) - always a bad sign. DBs are about relationships — Ňɏssa Pøngjǣrdenlarp, Mar 23 '18 at 17:54
I cant "Normalize" it. I cant manipulate the table at all. I just need help with my query — Timbo Slice, Mar 23 '18 at 17:57
The answer provided by @Jcl will solve exact matching. What you are looking for is fuzzy matching, and I have had good success [using the Levenshtein distance algorithm](https://stackoverflow.com/questions/921978/fuzzy-matching-using-t-sql), although it was not for phone numbers, so your milage will vary. — Guillaume CR, Mar 23 '18 at 18:06

Jcl · Answer 1 · 2018-03-23T18:13:15.673

1

Something like:

select * 
from dbase 
where 
     primaryphone = '444-444-1111'
  OR cellphone = '444-444-1111', 
  OR workphone = '444-444-1111'
  // etc.

It's a horrible design, but if it's a one-time thing...

Fuzzy-matching would need another approach and it's quite complicated. For regex you'd have to do a replace but I think mysql doesn't have any built-in regex replacing (again, you could build a custom regex function, but it'd not be simple).

You could do some simple replacements if you know the exact possible separators:

select * 
from dbase 
where 
     REPLACE(
       REPLACE(
         REPLACE(primaryphone,'-','')
         ,'(','')
      ,')','') = '4444441111'

  OR REPLACE(
       REPLACE(
         REPLACE(cellphone,'-','')
         ,'(','')
      ,')','') = '4444441111'

//etc.

For this, you'd need to pass the searched number without any symbols, and strip (using as much REPLACEs as you need) all the known symbols that could be in the records.

Again, horrible horrible design, but the query could be generated by a tool and if you can't change your database...

edited Mar 23 '18 at 18:13

answered Mar 23 '18 at 18:00

Jcl

27,696
5
61
92

Will try that out really quick! – Timbo Slice Mar 23 '18 at 18:06
Yeah, this was for a exact match only... fuzzy matching would take some extra work (I'd say not suitable for a begginer in mysql). I've added a simple replacement query... you'd need to input your phone without any symbols and replace the possible symbols outside the phone number you may find – Jcl Mar 23 '18 at 18:14
This worked! When I try with a different format 444-444-1111 vs 4444441111, I get nothing. Any chance i could use like inside of the where clause? – Timbo Slice Mar 23 '18 at 18:18
Sure, instead of `= '44444111'` you can use `LIKE '%444111%'` or whatever you may want. That's still not fuzzy-matching and would depend on your dataset (you may need more replacements, or whatnot)... but as a one-time thing it could do. What is important is that the data you are searching for contains no separators. You are basically saying "check if the string in this record field without all the separators is equal to this string", so if the record contains "444-111" you are saying: `check if 444111 = `. If your input string is "444-111" that evaluates to false – Jcl Mar 23 '18 at 18:19
Sorry I deleted the other comment, I had a typo in there. I think I need to have some like's in the where clause since the phone numbers can be formatted differently. – Timbo Slice Mar 23 '18 at 18:24
If you use this solution, it's important that the number you are searching for is stripped of all separators before searching. I explained why in the last comment (basically, it'll be checked against the number in the database without the separators) – Jcl Mar 23 '18 at 18:25
Thank you for you assistance! I have what I need now to complete the query. – Timbo Slice Mar 23 '18 at 18:30
You guys are awesome! This worked for me and is pulling everything I need Select * from dbase where REPLACE( REPLACE( REPLACE(primaryphone, '-','') ,'(','') ,')','') = '4444441111' – Timbo Slice Mar 23 '18 at 18:43

score 0 · Answer 2 · answered Mar 23 '18 at 18:12

0

In addition to @Jcl's answer, you can also strip out any noise from the fields with REPLACE in order to get only the numbers, which will increase your matching rate:

SELECT *
FROM dbase
WHERE REPLACE(primaryphone, '-', '') = '4444441111'

You can chain REPLACES to strip everything:

REPLACE(REPLACE(REPLACE('(444)444-1111', '-', ''), '(', ''), ')', '')

If your list of chars to strip gets really long, you can use a table variable

answered Mar 23 '18 at 18:12

Guillaume CR

3,006
1
19
31

Yeah, I was just adding this to my answer! :-) – Jcl Mar 23 '18 at 18:15
Where would the chain REPLACE go inside of the query, could you give me an example, because all of these numbers have ( or ) or / to separate the phone numbers. – Timbo Slice Mar 23 '18 at 18:33

score 0 · Answer 3 · answered Mar 23 '18 at 18:53

0

Instead of using or statement for all three columns of cell phone you can use CONCAT statement like below!

    SELECT *from posts 
    where    REPLACE(REPLACE(REPLACE(CONCAT(`primary_phone`,`workphone`,`cellphone`),'-',''),')',''),'(','')
    like '%xxxxxxxx%'

answered Mar 23 '18 at 18:53

najam khan

51
8

Ill will try this one out – Timbo Slice Mar 23 '18 at 19:21
This did not return any results for this query. I added all of my other fields inside of the () – Timbo Slice Mar 23 '18 at 19:36
have you searched for number inside like operator? – najam khan Mar 23 '18 at 19:48
I found the error in your statement. You cant have single quotes around the field. But this did work after I edited it. – Timbo Slice Mar 23 '18 at 19:55
SELECT * from dbase where REPLACE( REPLACE( REPLACE( CONCAT(primaryphone, workphone, cellphone,),'-',''),')',''),'(','') LIKE '%4444441111%' – Timbo Slice Mar 23 '18 at 19:56
what if primaryphone is `999444444` and workphone is `111122222`? when concatenated they will match the pattern `%4444441111%`. – Salman A Mar 23 '18 at 22:21
yes that will match in that case but that's too much rare. For that case you can use or operator or some other better solution. – najam khan Mar 26 '18 at 06:51

MySQL query to find a phone number in a table within multiple columns of the table

3 Answers3