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
[EMAIL PROTECTED]
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
[EMAIL PROTECTED]
Aleph Communications
ASTPP - Open Source Voip Billing & Calling Cards
www.aleph-com.net/astpp
_______________________________________________
--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