Mysql Query task to compare two table list values

Question

I have two tables. One user and the second contact

in the user table,

userId   phone
1        +91A12345678
2        +91A98765432
3        +1A1234
4        +91A2222

and in the contact table

 userId    contactNumber
    6         12345678
    7         +112345
    8         +912222

Now I want to remove A from the phone column of the user table and compare with the contactNumber of the contact table.

and I want this type of output

userId     ContactNumber
6          12345678
8          +912222

Anyone help me with this?

Answer 1

I see that you cannot get the expected result of your choice. You can try to combine all the functions. Please check the request.

select c.*
from contact_master c
join user_details u on replace(u.mobile, 'A', '') = c.contactNumber

union all

SELECT c.*
FROM contact_master c
INNER JOIN user_details u
    ON REPLACE(u.mobile, 'A', '') REGEXP c.contactNumber;

Answer 2

Use REGEXP:

SELECT *
FROM user u
INNER JOIN contact c
    ON REPLACE(u.phone, 'A', '') REGEXP c.contactNumber;