Search code examples
sqlmysqljoincastingsubstring

How 51=substring(051-3328328,1,3) in mysql


This Image contains Person and Country table I want to connect based on Phone number starting three characters and country code of country table

I Wrote this query for connecting these two table

select *
FROM person
    left join country on country_code=substring(phone_number,1,3)

Doubt-----

  1. How it is matching the 051 with 51 as 051 as substring is completely different from 51 as substring?

output for query by jarlh

enter image description here


Solution

  • Your main problem is that you are trying to equate 2 character codes like 51 with a 3 character codes like 051

    I would pick out the first 3 characters of the phone number with the substring() and convert them to an integer to do the comparison like this, then the leading zero in one side of the test will be dropped

    Also as you have a column called name in both tables, I would pick out the actual columns I want in the result specifically and alias them

    select p.name as persons_name, c.name as country_name, c.country_code, p.phone_number
    FROM person p
        left join country c on c.country_code = cast(substring(p.phone_number,1,3) AS UNSIGNED);
    
    Moncef      Morocco     212 212-1234567
    Maroua      Morocco     212 212-6543214
    Jonathan    Peru        51  051-1234567
    Meir        Israel      972 972-1234567
    Rachael     Israel      972 972-0011100