[Asterisk-Users] mysql phone number pattern match query

[Damon Estep]

Does anyone have a mysql query that will compare a number from the
asterisk cdr to a table of international country+city codes to determine
the closest match?

 

The two fields are;

 

1.	Asterisk mysql cdr 'dst' field - sample record value
'011441316551212'
2.	rate table data like this

 

DialPattern

011447977

011447979

011447980

011447981

011447984

011447985

011447986

011447987

011447988

011447989

011447990

011448

011449

01144

 

 

The goal is to find the _longest_ matching record from the rate table
for each dialed number. In this case '01144'

 

I am not a mySQL expert (obviously), my limited SQL experience is with
MS SQL where stored procedures and views are an option.

 

This is with mySQL 4.x, so no views.

 

Something like this

 

Select dialpattern from rates where left 5 match left 5 of dst

Order by length of dialpattern, descending

Compare dialpattern to the first x number of digits from dst where x =
the length of dial pattern

The first match (when ordered by length descending) is the correct
result (longest match)

 

Too bad mySQL does not understand English :-)

 

 

 

 

 

 

 

 

 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.digium.com/pipermail/asterisk-users/attachments/20060222/9943fcfa/attachment.htm