Dear Xperts

im relatively new to t-sql.

i have a table column which show the destination call no including country and area code with telephone...

DestinationNo=63927012345678 (first 2 to 8 digits may be the contry and their area or service provider code)

i have call rate table which has code and there rates.
code destination rate
-------------------------------------------
63 PHILIPPINES PROPER 0.6916
632 PHILIPPINES MANILA 0.6939
632240 PHILIPPINES MOBILE OTHER 0.7007
632246 PHILIPPINES MOBILE OTHER 0.7007
632248 PHILIPPINES MOBILE OTHER 0.7007
632249 PHILIPPINES MOBILE OTHER 0.7007
632250 PHILIPPINES MOBILE OTHER 0.7007
63927 PHILIPPINES MOBILE OTHER 0.876


for each destination no, have to take max 8 digits and loop
thru (8 then 7,6, 5, 4... 2 reverse order loop) to get the rate against the rate table.

i have millions of recs to get the rates value ... pls suggest a optimum solution with some stored procedure or function with source code.


Note::The input file is a legacy system, therefore NO changes expected...


Thanks a lot in advance

Regards