[Asterisk-Users] mysql phone number pattern match query

[Darren Wiebe]

What are the contents of your database? If you can put in a regex 
expression then I can tell you exactly how to do it, otherwise I can 
tell you close. In ASTPP, I'm doing it similar to how ASTCC does it. 
I'll lay it out here:

Pattern field in CDR
^1403.* will match anything beginning with 1403. Let's say you had 
dialed 14038880000. You would have a mysql query like this:
SELECT * FROM list WHERE '14038880000' RLIKE pattern ORDER by 
LENGTH(pattern) DESC

/blatant plug starts/
Are you building a billing system? If so, have a look at www.astpp.org, 
it has all this sort of stuff in place already.
/blatant plug ends/

Hope this helps

Darren Wiebe
darren at aleph-com.net


Damon Estep wrote:

> 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 J
>
>------------------------------------------------------------------------
>
>_______________________________________________
>--Bandwidth and Colocation provided by Easynews.com --
>
>Asterisk-Users mailing list
>To UNSUBSCRIBE or update options visit:
>   http://lists.digium.com/mailman/listinfo/asterisk-users
>  
>


-- 
Darren Wiebe
darren at aleph-com.net
Aleph Communications
ASTPP - Open Source Voip Billing & Calling Cards
www.aleph-com.net/astpp