[Asterisk-Users] Re: OT : For the SQL gurus..
On Mon, 10 Nov 2003 at 10:17, brez wrote: > > SELECT *, length(code) > > FROM a > > WHERE code = '00442085673456' > >OR code = '0044208567345' > >OR code = '004420856734' > >OR code = '00442085673' > >OR code = '0044208567' > >OR code = '004420856' > >OR code = '00442085' > >OR code = '0044208' > >OR code = '004420' > >OR code = '00442' > >OR code = '0044' > > ORDER BY length(code) DESC; > >Again, the first row in the result set is the one you are after. > > can be reduced to: > > SELECT TOP 1 *, length(code) > FROM a > WHERE code LIKE '0044%' > ORDER BY length(code) DESC; No it can't, because that would bring up everything that starts with 0044 (e.g. 00445678), but we are looking for the longest prefix that matches a given number. cu Reinhard ___ Asterisk-Users mailing list [EMAIL PROTECTED] http://lists.digium.com/mailman/listinfo/asterisk-users
Re: [Asterisk-Users] Re: OT : For the SQL gurus..
Thorsten Lockert wrote:
SELECT *, LENGTH(code)
FROM a
WHERE code = left('00442085673456', LENGTH(code))
ORDER BY LENGTH(code) DESC
LIMIT 1;
Awesome, thanks Thorsten..
___
Asterisk-Users mailing list
[EMAIL PROTECTED]
http://lists.digium.com/mailman/listinfo/asterisk-users
RE: [Asterisk-Users] Re: OT : For the SQL gurus..
SELECT *, LENGTH(code)
FROM a
WHERE code = left('00442085673456', LENGTH(code))
ORDER BY LENGTH(code) DESC
LIMIT 1;
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of WipeOut
Sent: Monday, November 10, 2003 9:33
To: [EMAIL PROTECTED]
Subject: Re: [Asterisk-Users] Re: OT : For the SQL gurus..
Reinhard Max wrote:
>On Mon, 10 Nov 2003 at 13:34, WipeOut wrote:
>
>
>
>>I guess I am going to have to look through the table multiple times
>>dropping the last digit on each select until I get a result..
>>
>>
>
>You could also try this one to see which one is faster:
>
> SELECT *, length(code)
> FROM a
> WHERE code = '00442085673456'
> OR code = '0044208567345'
> OR code = '004420856734'
> OR code = '00442085673'
> OR code = '0044208567'
> OR code = '004420856'
> OR code = '00442085'
> OR code = '0044208'
> OR code = '004420'
> OR code = '00442'
> OR code = '0044'
> ORDER BY length(code) DESC;
>
>Again, the first row in the result set is the one you are after.
>
>
>cu
> Reinhard
>
>
>
That works quite well.. result in 0.11 seconds..
I just have to work out now how to create the query in my code.. :)
Thanks a lot..
___
Asterisk-Users mailing list
[EMAIL PROTECTED]
http://lists.digium.com/mailman/listinfo/asterisk-users
___
Asterisk-Users mailing list
[EMAIL PROTECTED]
http://lists.digium.com/mailman/listinfo/asterisk-users
Re: [Asterisk-Users] Re: OT : For the SQL gurus..
Reinhard Max wrote: On Mon, 10 Nov 2003 at 13:34, WipeOut wrote: I guess I am going to have to look through the table multiple times dropping the last digit on each select until I get a result.. You could also try this one to see which one is faster: SELECT *, length(code) FROM a WHERE code = '00442085673456' OR code = '0044208567345' OR code = '004420856734' OR code = '00442085673' OR code = '0044208567' OR code = '004420856' OR code = '00442085' OR code = '0044208' OR code = '004420' OR code = '00442' OR code = '0044' ORDER BY length(code) DESC; Again, the first row in the result set is the one you are after. cu Reinhard That works quite well.. result in 0.11 seconds.. I just have to work out now how to create the query in my code.. :) Thanks a lot.. ___ Asterisk-Users mailing list [EMAIL PROTECTED] http://lists.digium.com/mailman/listinfo/asterisk-users
Re: [Asterisk-Users] Re: OT : For the SQL gurus..
> You could also try this one to see which one is faster: > > SELECT *, length(code) > FROM a > WHERE code = '00442085673456' > OR code = '0044208567345' > OR code = '004420856734' > OR code = '00442085673' > OR code = '0044208567' > OR code = '004420856' > OR code = '00442085' > OR code = '0044208' > OR code = '004420' > OR code = '00442' > OR code = '0044' > ORDER BY length(code) DESC; > > Again, the first row in the result set is the one you are after. ...or you could do the 'only right thing': - Move to pgSQL and write a stored procedure to do it roy ___ Asterisk-Users mailing list [EMAIL PROTECTED] http://lists.digium.com/mailman/listinfo/asterisk-users
[Asterisk-Users] Re: OT : For the SQL gurus..
On Mon, 10 Nov 2003 at 14:55, Michael Bielicki wrote: > Just as a tip you could do that with regex functions as well, > omitting the quite costly length functions .. Well, regular expressions can be costly as well, and it depends on the DBMS implementation how costly length() is. If the actual length of a text or varchar field is being stored anyways, length() can be implemented in a rather cheap way. cu Reinhard ___ Asterisk-Users mailing list [EMAIL PROTECTED] http://lists.digium.com/mailman/listinfo/asterisk-users
[Asterisk-Users] Re: OT : For the SQL gurus..
On Mon, 10 Nov 2003 at 13:34, WipeOut wrote: > I guess I am going to have to look through the table multiple times > dropping the last digit on each select until I get a result.. You could also try this one to see which one is faster: SELECT *, length(code) FROM a WHERE code = '00442085673456' OR code = '0044208567345' OR code = '004420856734' OR code = '00442085673' OR code = '0044208567' OR code = '004420856' OR code = '00442085' OR code = '0044208' OR code = '004420' OR code = '00442' OR code = '0044' ORDER BY length(code) DESC; Again, the first row in the result set is the one you are after. cu Reinhard ___ Asterisk-Users mailing list [EMAIL PROTECTED] http://lists.digium.com/mailman/listinfo/asterisk-users
