comparing countrycode list - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

comparing countrycode list

Author

Topic

ahmadjamalkhan
Starting Member

36 Posts

Posted - 2010-03-15 : 14:17:25

Hi
I am trying to build a digital directory service.
Table A has one columns ,
ContactNumber starting with countrycode (eg.442071977543)

Table B has two column,
Countrycode ,
CountryDescription.

I am trying to compare calledNumber against TableB to provide me information about countryDescription,

e.g
442071977543 , 44 , UnitedKingdom
16035677643 , 1 , USA
27786537273 , 27 , southafrica
96652524319 , 9665, UAE

since all countrycode has different lengh prefix , i can not simply use substring function on contactnumber and join it with countrycode table.

It appears has contactnumber has to be matched with all countrycode table and the longest digit match should be the coutry associated.

I will appreciate your help

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-16 : 00:43:37

[code]
SELECT a.ContactNumber,b.CountryCode,b.CountryDescription,LEN(STUFF(a.ContactNumber,PATINDEX( b.CountryCode + '%',a.ContactNumber),LEN(b.CountryCode),'')) AS ResidualLen
INTO #Temp
FROM TableA a
JOIN TableB b
ON a.ContactNumber LIKE b.Countrycode + '%'

SELECT t.ContactNumber,t.CountryCode,t.CountryDescription
FROM #Temp t
INNER JOIN (SELECT ContactNumber,MIN(ResidualLen) AS MinLen
FROM #Temp
GROUP BY ContactNumber)t1
ON t1.MinLen=t1.ResidualLen
AND t1.ContactNumber = t.ContactNumber
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2010-03-16 : 04:48:17

Visakh: Can't you just use LEN of TableB.CountryCode DESC to get best match? Pick the first (longest) one for each ROW (I'd do the code, but I am pants at that construction! OVER ROW_NUMBER() or possibly CROSS APPLY?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-16 : 05:14:12

quote:
Originally posted by Kristen

Visakh: Can't you just use LEN of TableB.CountryCode DESC to get best match? Pick the first (longest) one for each ROW (I'd do the code, but I am pants at that construction! OVER ROW_NUMBER() or possibly CROSS APPLY?

Sure that was my first thought too. But I didnt go by it as this is a SQL 2000 forum

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2010-03-16 : 06:16:42

I'm glad that you are awake then!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-16 : 06:38:38

quote:
Originally posted by Kristen

I'm glad that you are awake then!!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ahmadjamalkhan
Starting Member

36 Posts

Posted - 2010-03-17 : 08:15:41

Hi Visakh
Thanks for your help.
Initially it gave me an error on ResidualLen , I slightly changed the code and it works great

SELECT a.ContactNumber,b.CountryCode,b.CountryDescription,
LEN(STUFF(a.ContactNumber,PATINDEX( b.CountryCode + '%',a.ContactNumber),LEN(b.CountryCode),'')) AS ResidualLen
INTO #Temp
FROM TableA a
JOIN TableB b
ON a.ContactNumber LIKE b.Countrycode + '%'

SELECT t.ContactNumber,t.CountryCode,t.CountryDescription,t.ResidualLen
FROM #Temp t
INNER JOIN (SELECT ContactNumber,MIN(ResidualLen) AS MinLen
FROM #Temp
GROUP BY ContactNumber)t1
ON t1.MinLen=t.ResidualLen
AND t1.ContactNumber = t.ContactNumber

AKhan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-17 : 10:03:35

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources