Suggestion to add "locate" as a broader version of "instr"

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Suggestion to add "locate" as a broader version of "instr"

Max Vlasov
Hi,

Some time ago when there was no "instr" functions, I looked at Mysql help
pages and implemented a user function "locate" as the one that allows
searching starting a particular position in the string. With two parameters
form it was just identical to "instr" only the order of parameters was
reversed. As I see, the latest sqlite has only "instr".

It's not a big deal, but I noticed that "locate" with three parameters
becomes convenient for CTE recursive queries since it allows search
sequentially in the string. For example, a little bulky at last, but I
managed to do "comma-list to dataset" query

I suppose implementing "locate" and doing "instr" as a call to "locate"
would cost the developers probably no more than a hundred of bytes for the
final binary

Thanks

Max
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Suggestion to add "locate" as a broader version of "instr"

big stone
+1 .

A few more 'classic/simple' sql instructions would not be a bad thing :
sqrt(), locate(substring, string, start), ...
They are not in a sql official "normalisation", but :
-  '%' is not either,
- avg() looks a little bit incomplete without sqrt().
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Suggestion to add "locate" as a broader version of "instr"

Gabor Grothendieck
In reply to this post by Max Vlasov
On Fri, Feb 14, 2014 at 2:33 PM, Max Vlasov <[hidden email]> wrote:

> Hi,
>
> Some time ago when there was no "instr" functions, I looked at Mysql help
> pages and implemented a user function "locate" as the one that allows
> searching starting a particular position in the string. With two parameters
> form it was just identical to "instr" only the order of parameters was
> reversed. As I see, the latest sqlite has only "instr".
>
> It's not a big deal, but I noticed that "locate" with three parameters
> becomes convenient for CTE recursive queries since it allows search
> sequentially in the string. For example, a little bulky at last, but I
> managed to do "comma-list to dataset" query
>
> I suppose implementing "locate" and doing "instr" as a call to "locate"
> would cost the developers probably no more than a hundred of bytes for the
> final binary


Parsing fields is also done with substring_index in MySQL and having both locate
and substring_index would be useful for MySQL compatibility.

Parsing fields created using group_concat is one particular example.
One related
item is that in MySQL group_concat can specify the order of rows to be
concatenated
as well as a number of other aspects not currently available in SQLite.

--
Statistics & Software Consulting
GKX Group, GKX Associates Inc.
tel: 1-877-GKX-GROUP
email: ggrothendieck at gmail.com
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users