CODEWITHSUNDEEP
t-sql
Myk Syk
Myk Syk

Reputation: 71

How to join two tables for phone codes

I have the two tables.

The first table tbl1:

name nvarchar(255)
number nvarchar(255)

The second table dbo.phone_codes:

country nvarchar(255)
code nvarchar(4)

The first query:

Select Name, Number
from dbo.tbl1

I am getting this result:

User1   375xxxxxxxx
User1   7xxxxxxxxxx
User2   49xxxxxxxxx

The second query:

select country, code
from dbo.phone_codes

I am getting result:

Belarus 375
Russian 7
Germany 49
Poland  48

What should I use the query if I want get this result:

User1   37552222222 Belarus 375
User1   77333333333 Russian 7
User2   49111111111 Germany 49

The first table:

name - nvarchar(255)
number - nvarchar(255)

The second table:

country - nvarchar(255)
code - nvarchar(4)

Upvotes: 0

Views: 351

Answers (3)

Anup Agrawal
Anup Agrawal

Reputation: 6669

Assuming your phone number and country code consists only of numbers, no spaces, brackets, dashes or plus sign. You can try something like this: 

SELECT *
  FROM( 
        SELECT T.Name , 
               T.Number ,
               P.country ,
               P.code ,                    
               RANK() OVER( PARTITION BY T.Number 
                            ORDER BY ISNULL(CAST(P.code AS int), 1) DESC)RNK
          FROM
               dbo.tbl1 T LEFT JOIN dbo.phone_codes P
               ON T.Number LIKE P.Code + '%'
      )A
  WHERE A.RNK = 1;

If you have special characters, you need to use replace function to remove any non-numeric characters.

Rank function is used to resolve the cases like Bermuda (1441) and US(1).

Upvotes: 0

Kapol
Kapol

Reputation: 6463

SELECT
    t.Name, t.Number, p.Country, p.Cpde     
FROM
    dbo.tbl1 t, dbo.phone_codes p
WHERE
    charindex(p.Code, t.Number) = 1

Upvotes: 0

EricZ
EricZ

Reputation: 6205

Try this

SELECT 
 t.Name, t.Number, p.country, p.code
FROM dbo.tbl1 t
INNER JOIN dbo.phone_codes p
  ON t.Number LIKE p.code + '%'

Upvotes: 1

Related Questions