Question about a query

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

Question about a query

Leonardo Inácio de Freitas
Hello!
Using SQLite, can you use masks (or regex) (like '% str%') inside
instr / substr, to delimit the output of a select, instead of me
determining the beginning and end of the substring?

What I need (example): SELECT (substr (my_field, like% abc,
until_next_spacechar_after (like% abc))) from my_table

That is, after getting the start of the substring using a like mask, I
want it from this point, the end of the substring to be the next
character space.

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

Re: Question about a query

Simon Slavin-3
On 9 Oct 2018, at 2:47pm, Leonardo Inácio de Freitas <[hidden email]> wrote:

> Using SQLite, can you use masks (or regex) (like '% str%') inside
> instr / substr, to delimit the output of a select, instead of me
> determining the beginning and end of the substring?

No.  Sorry.  You have to use string core functions to isolate the piece you want:

<https://www.sqlite.org/lang_corefunc.html#substr>

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

Re: Question about a query

Brian Curley
well...

It's not quite that categorical "no", although Simon's more than correct.

There's extensions that allow for regexp(), such as you might find in
SQLite Studio that allow for some really handy cross-functionality when
paired with group_concat(), for example. (It's addictive to have it handy
during development, in fact.) I understand that it can be rolled into your
own local build, if you want to use it, but it complicates things in terms
of portability. If you craft your SQL around that...you need to bring that
build along with it, and any headaches that it might include.

Otherwise, you'd want to leverage your application's handling to emulate
the same behavior.

Regards.

Brian P Curley


On Tue, Oct 9, 2018 at 10:04 AM Simon Slavin <[hidden email]> wrote:

> On 9 Oct 2018, at 2:47pm, Leonardo Inácio de Freitas <
> [hidden email]> wrote:
>
> > Using SQLite, can you use masks (or regex) (like '% str%') inside
> > instr / substr, to delimit the output of a select, instead of me
> > determining the beginning and end of the substring?
>
> No.  Sorry.  You have to use string core functions to isolate the piece
> you want:
>
> <https://www.sqlite.org/lang_corefunc.html#substr>
>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Question about a query

Jens Alfke-2
In reply to this post by Leonardo Inácio de Freitas


> On Oct 9, 2018, at 6:47 AM, Leonardo Inácio de Freitas <[hidden email]> wrote:
>
> Using SQLite, can you use masks (or regex) (like '% str%') inside
> instr / substr, to delimit the output of a select, instead of me
> determining the beginning and end of the substring?

You could implement a custom query function to do this (custom functions are quite simple, and there are examples online).

Or you could just postprocess the string in your application code when it comes back from the query.

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

Re: Question about a query

James K. Lowden
On Tue, 9 Oct 2018 10:22:12 -0700
Jens Alfke <[hidden email]> wrote:

> You could implement a custom query function to do this (custom
> functions are quite simple, and there are examples online).

        http://www.schemamania.org/sql/sqlite/udf/

Been there, done that.  :-)

--jkl

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users