Query Puzzle in SQL Server

[elmo29]

I have a list of tariffs to apply to a phonenumber. The tariffs are dependent on the prefix of the phonenumber - which can be variable in length.

So I have a table containing the phone number and a table containing the prefix .... how do I do a join on this?

So I have 012160909808, field2, field3, field4 in the phonenumber table to join to 0121 in the tariff table.

I want to do something like

SELECT table1.phonenumber, table2.prefix
from table1, table2
WHERE phonenumber is like 'prefix%'

But this doesn't work for obvious reasons...

Any ideas how I would go about this? [sig][/sig]

 

[MikeLacey]

if the prefix is fixed length you could join the tables based on a substr - would be a bit slow though if the tables are long [sig]<p>Mike<br><a href=mailto:michael.j.lacey@ntlworld.com>michael.j.lacey@ntlworld.com</a><br><a href=

http://www.cargill.com/>

Cargill's Corporate Web Site</a><br> [/sig]

 

[listener22]

If you can, add a column for just the prefixes. [sig]<p>Jim<br><a href= > </a><br>oracle, vb, some javascript[/sig]

 

[Guest_imported]

The problem you have is a common one in telephone billing systems, the way it is handled is to work through the phone number character by character until a match with a prefix is found. This is done programatically, normally, as you may have to deal with international numbers as well as local e.g. &quot;01268nnnnnnn&quot; may be a US number (01 is the prefix for the States, and the Carribbean).

You could simulate a simplified version of this in straight SQL using a UNION with a substring function as follows (I'm assuming you're using SQLServer or Access here but if not, the syntax will be similar you'll just have to modify the &quot;Top 1&quot; clause or the substring clause).

Select Top 1 (Select Len(Prefix), Prefix, Tariff
from tariff_table a, phone_number_table b
where a.Prefix = substring(phone_no, 1, 2)

Union

Select Len(Prefix), Prefix, Tariff
from tariff_table a, phone_number_table b
where a.Prefix = substring(phone_no, 1, 3)

Union

etc.. keep adding selects with substrings that increase by 1
until you have covered the maximum prefix you're dealing with.

)
Order by 1 Asc

The &quot;Order by&quot; and the &quot;Top 1&quot; are just to ensure that if you match on more than one prefix (which may be possible) then the shortest match is the one that determines the tariff.