Compare phone numbers from two tables with (-) symbol-CodePudding

This is my query for selecting table from MAJ that exist in the table user. However, both tables have two different format numbers to compare.

Phone number which is notel in user have (-) symbol , eg:019-9910922 and phone number in MAJ doesn't have that symbol, eg:0199910922.

Is there any other ways or solution where I can compare phone numbers(notel) in correct way?

SELECT * FROM MAJ WHERE EXISTS ( select * from user B WHERE A.notel = B.notel) AND SUBSTR('notel', 7,5);

CodePudding user response：

You can use the REPLACE mysql function to remove the - during comparison

I am unable to understand which of your tables (A or B) is the one with the -, but let's say it's B then your query becomes something like

SELECT * FROM MAJ WHERE EXISTS ( select * from user B WHERE A.notel = REPLACE(B.notel, "-", "")) AND SUBSTR('notel', 7,5);

The focus part is

REPLACE(B.notel, "-", "")

You simply take the original value (which contains -) and replace - with nothing.

This way you can compare the numbers.