Nice, I just converted this to Ruby/Sequel.<br><br>DB[:rates].first{{prefix=>substring('number', 1, length(prefix))}}<br><br>Thanks for the help :).<br><br><div class="gmail_quote">On Wed, Oct 14, 2009 at 2:07 AM, TTNC - Adnan Barakat <span dir="ltr"><<a href="mailto:technical@ttnc.co.uk">technical@ttnc.co.uk</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"><div class="im">Diego Viola wrote:<br>
> I'm using MySQL now but I will try PostgreSQL with the prefix module, is<br>
> there a way to do that without the prefix module and with regular SQL?<br>
><br>
> Any examples?<br>
</div>SELECT * FROM rates WHERE prefix = SUBSTRING('$NUMBER$', 1,<br>
LENGTH(prefix)) LIMIT 1<br>
<br>
Adnan<br>
<div class="im"><br>
> Diego<br>
><br>
> On Tue, Oct 13, 2009 at 10:45 PM, Even André Fiskvik <<a href="mailto:grevenx@me.com">grevenx@me.com</a><br>
</div><div><div></div><div class="h5">> <mailto:<a href="mailto:grevenx@me.com">grevenx@me.com</a>>> wrote:<br>
><br>
> What database are you using?<br>
> You could do this with regular SQL, but it would by a costly operation,<br>
> for PostgreSQL we're using the prefix module:<br>
> <a href="http://pgfoundry.org/projects/prefix/" target="_blank">http://pgfoundry.org/projects/prefix/</a><br>
><br>
> You can then match the closest prefix by using something like<br>
> "WHERE myprefix_col @> caller_destination_number ORDER BY LENGTH<br>
> (myprefix_col::text) LIMIT 1;"<br>
><br>
><br>
> Best regards,<br>
> Even André<br>
><br>
><br>
> On 13. okt. 2009, at 23.53, Diego Viola wrote:<br>
><br>
> > Hello,<br>
> ><br>
> > I'm trying to write a post-paid billing script, I have the CDR on my<br>
> > database and also a "rates" table, the CDR contains fields like<br>
> > caller_destination_number, variable_duration, etc. and the rates<br>
> > table contains: destination, prefix, rate (cost).<br>
> ><br>
> > The problem is that I can't just strip the destination number to<br>
> > take the prefix from it because I have to deal with destination<br>
> > numbers from different countries and they all have different prefix<br>
> > lengths... so I need to find another way to take the prefix from the<br>
> > destination number.<br>
> ><br>
> > Any ideas how to do this?<br>
> ><br>
> > Thanks,<br>
> ><br>
> > Diego<br>
> ><br>
> > _______________________________________________<br>
> > FreeSWITCH-users mailing list<br>
> > <a href="mailto:FreeSWITCH-users@lists.freeswitch.org">FreeSWITCH-users@lists.freeswitch.org</a><br>
</div></div>> <mailto:<a href="mailto:FreeSWITCH-users@lists.freeswitch.org">FreeSWITCH-users@lists.freeswitch.org</a>><br>
<div class="im">> > <a href="http://lists.freeswitch.org/mailman/listinfo/freeswitch-users" target="_blank">http://lists.freeswitch.org/mailman/listinfo/freeswitch-users</a><br>
> > UNSUBSCRIBE:<a href="http://lists.freeswitch.org/mailman/options/freeswitch-" target="_blank">http://lists.freeswitch.org/mailman/options/freeswitch-</a><br>
> > users<br>
> > <a href="http://www.freeswitch.org" target="_blank">http://www.freeswitch.org</a><br>
><br>
><br>
> _______________________________________________<br>
> FreeSWITCH-users mailing list<br>
> <a href="mailto:FreeSWITCH-users@lists.freeswitch.org">FreeSWITCH-users@lists.freeswitch.org</a><br>
</div>> <mailto:<a href="mailto:FreeSWITCH-users@lists.freeswitch.org">FreeSWITCH-users@lists.freeswitch.org</a>><br>
<div><div></div><div class="h5">> <a href="http://lists.freeswitch.org/mailman/listinfo/freeswitch-users" target="_blank">http://lists.freeswitch.org/mailman/listinfo/freeswitch-users</a><br>
> UNSUBSCRIBE:<a href="http://lists.freeswitch.org/mailman/options/freeswitch-users" target="_blank">http://lists.freeswitch.org/mailman/options/freeswitch-users</a><br>
> <a href="http://www.freeswitch.org" target="_blank">http://www.freeswitch.org</a><br>
><br>
><br>
><br>
> ------------------------------------------------------------------------<br>
><br>
> _______________________________________________<br>
> FreeSWITCH-users mailing list<br>
> <a href="mailto:FreeSWITCH-users@lists.freeswitch.org">FreeSWITCH-users@lists.freeswitch.org</a><br>
> <a href="http://lists.freeswitch.org/mailman/listinfo/freeswitch-users" target="_blank">http://lists.freeswitch.org/mailman/listinfo/freeswitch-users</a><br>
> UNSUBSCRIBE:<a href="http://lists.freeswitch.org/mailman/options/freeswitch-users" target="_blank">http://lists.freeswitch.org/mailman/options/freeswitch-users</a><br>
> <a href="http://www.freeswitch.org" target="_blank">http://www.freeswitch.org</a><br>
<br>
<br>
_______________________________________________<br>
FreeSWITCH-users mailing list<br>
<a href="mailto:FreeSWITCH-users@lists.freeswitch.org">FreeSWITCH-users@lists.freeswitch.org</a><br>
<a href="http://lists.freeswitch.org/mailman/listinfo/freeswitch-users" target="_blank">http://lists.freeswitch.org/mailman/listinfo/freeswitch-users</a><br>
UNSUBSCRIBE:<a href="http://lists.freeswitch.org/mailman/options/freeswitch-users" target="_blank">http://lists.freeswitch.org/mailman/options/freeswitch-users</a><br>
<a href="http://www.freeswitch.org" target="_blank">http://www.freeswitch.org</a><br>
</div></div></blockquote></div><br>