search? - 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

SQL Server Development (2000)

search?

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-02-19 : 13:18:36

Johnd writes "I've just moved up to sql2000 and wanted to know if there was a way to find telephone numbers that marry to telephone codes;

table one=

destination name       code

hinckley               014554
barwell                01132
barwell2               011325
leices                 01162

table two=

numberdialled          price

014554445555           0.25
011625895654           0.39

There may be hundreds of codes and numbersdialled.

Any help would be helpful.

Thank you"

yakoo
Constraint Violating Yak Guru

312 Posts

Posted - 2002-02-19 : 13:32:07

You could try this


SELECT * FROM table_one t1
     INNER JOIN table_two t2 
     ON t1.code = Left(t2.numberdialled, Len(t1.code))

yakoo
Constraint Violating Yak Guru

312 Posts

Posted - 2002-02-19 : 13:36:14

Actual Im not sure if that is going to work.

im assuming that all numberdialled numbers will be 12 characters.

if you have a code of 01455 should 014554445555 be returned and should the code of 014554 return it as well.

I noticed this with your barwell and barwell2 codes.

dsdeming

479 Posts

Posted - 2002-02-19 : 13:51:40

I think this will do it:

set nocount on
create table #aa
(destination varchar( 30 ), code varchar( 30 ))

insert into #aa select 'hinckley', '014554'
insert into #aa select 'barwell', '01132'
insert into #aa select 'barwell2', '011325'
insert into #aa select 'leices', '01162'

create table #bb
(numberdialled varchar( 30 ), price smallmoney )

insert into #bb select '014554445555', 0.25
insert into #bb select '011625895654', 0.39
insert into #bb select '011325895654', 0.21 --barwell2
insert into #bb select '011326895654', 0.22 --barwell

SELECT o2.destination,
a.code,
a.numberdialled,
a.price
FROM #aa o2
JOIN ( SELECT code = MAX( o.code ), t.numberdialled, t.price
FROM #aa o
JOIN #bb t on o.code = left( t.numberdialled, len( o.code ))
GROUP BY t.numberdialled, t.price ) a
on o2.code = a.code

drop table #aa
drop table #bb

dsdeming

479 Posts

Posted - 2002-02-19 : 14:04:26

I see that I just pasted up all the test code I was using. All you should have to do is replace the table and column names in my select statement with your own. The table creation and population was for development only.

nr
SQLTeam MVY

12543 Posts

Posted - 2002-02-19 : 14:13:08

I'm assuming that you want to find the longest code that matches the number dialled.
i.e. 01132, 011325 you want 011325 rather than 01132 if that is the number dialled. And you probably have 01 to catch all the numbers for which you don't have a dialing code.

Hundreds of dialling codes is an understatement if you are working on an international system.
I would batch up the numbers that you are trying to match so that you can tailor the query to the amount of memory available.

It is also handy if you have the dialling code length as another field on the dialing code table.

update tbl
set codeID =
(select id from diallingcodes where diallednumber like code + '%' and codelen = (select max(codelen) from diallingcodes where diallednumber like code + '%'))

I usually do this using a loop though - it will be faster as it can use an index

decalare @i int
select @i = max(codelength) from diallingcodes
while @i > 0
update tbl
set codeid = id
from diallingcodes
where left(diallednumber,@i) = code
and codeid = 0
set @i = @i-1
end

For this it is important to batch the calls up so that you are not working on millions at a time.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

TheMilkMan
Starting Member

10 Posts

Posted - 2002-02-20 : 11:12:30

Thanks guys, helped a lot

Getting There

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources