November 14, 2006 at 4:19 pm
Fellow SQLers,
I am using this function to return an international country given the complete International telephone number. It uses a variety of cursors and is therefore quite slow, but I think accurate. Might there be a simpler way or a set based solution to do this?
Here is the function: (Sorry about the pagination and indents)
I can send sample data for the Country Table if you need it. The Structure is:
(Country_CD varchar(3),Country varchar(100),city_cd varchar(5)
/* Function Returns Country Name.
Use: select carrierrates.dbo.fn_GetInternationalCountry '43456455511111')
*/
CREATE FUNCTION fn_GetInternationalCountry(@ANI char(25))
RETURNS varchar(100) AS
Begin
/* Check to see if the country is a 3 char country code */
Declare
@Country_Cd char(3),
@City_Cd char(5),
@Length as int ,
@Country as varchar(10),
@ANIStr as Varchar(10),
@CityCountry as varchar(100),
@sql as varchar (1000)
if len(@ani) > 10
Begin
DECLARE curCountry Cursor for Select Country_Cd from carrierrates.dbo.International with (NOLOCK) where Country_Cd = left(@ANI,3) and city_cd <> ''
open curCountry
fetch next from curCountry into @country_cd
if @country_cd is null
begin
Close curCountry
deallocate curCountry
declare curCountry Cursor for Select Country_Cd from carrierrates.dbo.International with (NOLOCK) where Country_Cd = left(@ANI,2)
open curCountry
fetch next from curCountry into @country_cd
if @country_cd is null
begin
Close curCountry
deallocate curCountry
declare curCountry Cursor for Select Country_Cd from carrierrates.dbo.International with (NOLOCK) where Country_Cd = left(@ANI,1)
open curCountry
fetch next from curCountry into @country_cd
end
end
if @country_cd is not null
begin
Close curCountry
deallocate curCountry
declare curCountry Cursor for Select rtrim(City_Cd) as City_Cd from carrierrates.dbo.International with (NOLOCK) where rtrim(Country_Cd) = rtrim(@country_cd)
open curCountry
set @city_cd = ''
fetch next from curCountry into @city_cd
WHILE @@FETCH_STATUS = 0 --As Long as we got data keep going.
begin
if @city_cd is not null
begin
set @Country = rtrim(@Country_Cd) + rtrim(@city_cd)
set @length = Len(rtrim(@country_cd))+Len(rtrim(@city_cd))
set @ANIStr = substring(@ani,1,@length)
if @Country = @ANIStr
Begin
Declare curCityCountry Cursor for select Country from CarrierRates.dbo.International where Country_cd = @Country_cd and City_Cd = @City_cd
open curCityCountry
fetch next from curCityCountry into @CityCountry
Close curCityCountry
deallocate curCityCountry
End
end
fetch next from curCountry into @city_cd
end
end
Close curCountry
deallocate curCountry
end
if @citycountry is null
begin
set @citycountry = 'Not Identified '
end
return @CityCountry
end
thanks,
Steve
November 14, 2006 at 4:53 pm
Yeah, you're bloody right. Terrible thing.
Try this:
Select Country
FROM CarrierRates.dbo.International R
WHERE R.Country_Cd = SUBSTRING(@ANI, 1, LEN(R.Country_CD))
AND R.City_Cd = SUBSTRING(@ANI, LEN(R.Country_CD)+1, LEN(R.City_Cd))
If you have computed column for Country_CD + City_CD with index on it it could improve performance and simplify the query.
Be careful! Not tested at all. Check it out against your actual data structure and run for all possible variations!
But at least it will give you an idea.
_____________
Code for TallyGenerator
November 15, 2006 at 8:30 am
Serqiy,
Holy smokes! You came pretty close on the first shot.
I am astounded at how simple set solutions can be. The other is certianly a mess.
Perhaps I should clarify. When we have a dial string, we have no idea how many characters the country has in it, 1-3. City code may be null or from 1-5 characters. For example, a dial string of 23615219999 may have many possibilities but only one combination which is correct.
The country may be 2, 23, or 236 , if it is 236, then the city code could be null,1,13,152,1521 or 15219. If it is 23, then the city codes might be null,6,61,615,6152 or 61521. All we have is the dialed number, and only one combination of digits is possible for a connected call. So first we must find a valid country, then find a country, citycode combination that works. If no citycode works, then the one with the null city_cd value is used.
I tested you code on this dialed number: 32475847392
declare @ani varchar(25)
set @ani = '32475847392'
Select * FROM CarrierRates.dbo.International R
WHERE R.Country_Cd = SUBSTRING(@ANI, 1, LEN(R.Country_CD))
AND R.City_Cd = SUBSTRING(@ANI, LEN(R.Country_CD)+1, LEN(R.City_Cd))
AND Carrier = 'QWEST'
the return is: (Carrier, country_cd, country, city_cd)
QWEST 32 Belgium
QWEST 32 Belgium - Mobile/Special Services 47
This call was a Cell phone call or city_cd (47) but two records were returned. In otherwords, the Null city_cd met the conditions as well as the 47. I need it so only a single record meets the test. In other words, if all city codes fail, then it will return only the country with null city_cd. I suppose we could sort by city_cd desc and use a top 1 query. What do you think?
November 15, 2006 at 8:39 am
>>All we have is the dialed number, and only one combination of digits is possible for a connected call.
I think that is a very important fact and should result in a different data model ?
Rather than parsing up the dialed number *every time*, why not have a table like this (data types omitted):
Create Table ValidPhoneNumbers (
DialedNumber,
CountryCode,
CityCode,
PhoneNumber
)
Do the work once populating this table, unique index it on DialedNumber. Do a fast indexed lookup in this table to retrieve the 3 parts of any dialed number, rather than parsing over & over again.
November 15, 2006 at 8:46 am
I don't quite understand. Do you mean build a lookup table with all the possible valid combinations and use this for the source table instead of the International table? International City_codes change almost daily, with 50000-100000 people calling all around the world every day and this being computed on the fly. Might work to build an Intl_Lookup table for this function after updating the International table. I will look into it. Thanks.
Steve
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply