SQL Query Failing to Parse some Numbers for CRM Integration

[TaylorStevens23]

I am having an issue parsing some of the phone data from our database using the mySQL CRM Integration. The data comes in different forms. Our workphonenumber is formatted like ["(XXX) XXX-XXXX"]. While our homephone numbers in the db are formatted like (XXX) XXX-XXXX.

When I run the following SQL Lookup Statement "SELECT `index` as contactid, firstname as firstname, lastname as lastname, workemail as email, workphonenumber as phonebusiness, homephone as phonemobile, homephone as faxbusiness FROM database WHERE REPLACE(REPLACE(REPLACE(REPLACE(workphonenumber, '(', ''), ')', ''), '-', ''), ' ', '') LIKE '%[Number]%' or REPLACE(REPLACE(REPLACE(REPLACE(homephone, '(', ''), ')', ''), '-', ''), ' ', '') LIKE '%[Number]%'or homephone LIKE '%[Number]%'" I can successfully locate the "contact" from our database for homephone


But if I try to locate a number listed in workphonenumber field of the db it seems to find the person but fails to recognize it as a "contact".


My initial thought is that the query is failing to parse out the characters ",[, and ]. So I tried to expand my replace string to include the removal of those charcters with the following string WHERE REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(workphonenumber, '"', ''), ']', ''), '[', ''), '(', ''), ')', ''), '-', ''), ' ', '') LIKE '%[Number]%' but that seems to break my query.

Do you see any issues with the SQL query/replacement string? Or do you have any thoughts on what I could do differently with the query?

 

[Evolute IT]

You'd be better off using Regex instead of chaining replace calls.

https://dev.mysql.com/doc/refman/8.0/en/regexp.html

Replace anything that is not a digit in the field.

 

[TaylorStevens23]

You'd be better off using Regex instead of chaining replace calls.

https://dev.mysql.com/doc/refman/8.0/en/regexp.html

Replace anything that is not a digit in the field.

Thanks for the suggestion! I will give this a try