[Asterisk-Users] Re: [Users] OT : For the SQL gurus..

[Reinhard Max]

On Mon, 10 Nov 2003 at 09:56, WipeOut wrote:

> If I have a MySQL table with dialing codes and a corresponding
> description (see below) and I want to lookup the best match for a
> phone number.. What would the SQL look like to do it?

I don't know MySQL very well, but maybe you also get an idea of how it
can be done if I describe it in terms of PostgreSQL:

 SELECT DISTINCT *,length(code)
 FROM a
 WHERE '00442085673456' LIKE (code || '%')
 ORDER length(code) DESC;

Now, the first row in the result set contains the longest matching
prefix of the given number that exists in the table, if there is any.

> or would it take more than just SQL to get to the best result?

If MySQL doesn't support using a column as a pattern for the LIKE
operator you might have to make multiple queries and allways strip one
more digit from the end of the number until you find a match.

cu
	Reinhard