Re: [sqlite] Question about a query

[James K. Lowden]

On Tue, 9 Oct 2018 10:22:12 -0700
Jens Alfke  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about a query

[Jens Alfke]

> On Oct 9, 2018, at 6:47 AM, Leonardo Inácio de Freitas 
>  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 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  wrote:

> On 9 Oct 2018, at 2:47pm, Leonardo Inácio de Freitas <
> oldbrain...@gmail.com> 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:
>
> 
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about a query

[Simon Slavin]

On 9 Oct 2018, at 2:47pm, Leonardo Inácio de Freitas  
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:



Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about expected query result??

[Brass Tilde]

> > On MS SQL Server 2000, one can pass a field name to the COUNT function,
and
> > though I haven't yet seen any difference in the results between the two,
the
> > queries run faster with COUNT() than with COUNT(*).
>
> COUNT(fieldname) provides the count of rows where the data in 'fieldname'
is
> non-null.  COUNT(*) provides the total count of rows:

Ah.  I guess that pretty much explains that.  I seldom do counts without a
WHERE clause of some sort that would preclude counting nulls in the first
place.

Thanks.


-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

[sqlite] Question about expected query result??

[Shawn Anderson]

I have the following table/data

ClientIPClientDomain
63.149.28.33mail.serverlocation.com
63.149.28.33mail.serverlocation.com
63.149.28.33mail.serverlocation.com
63.149.28.33mail.serverlocation.com
211.141.67.7xmailserver.org
211.141.67.7xmailserver.org
211.141.67.7xmailserver.org
68.80.189.111   ravensorb
128.205.7.58acsu.buffalo.edu
66.35.250.206   sc8-sf-list1.sourceforge.net
128.205.7.57acsu.buffalo.edu
128.205.7.57acsu.buffalo.edu
128.205.7.57acsu.buffalo.edu
128.205.7.57acsu.buffalo.edu
128.205.7.57acsu.buffalo.edu
128.205.7.57acsu.buffalo.edu
128.205.7.57acsu.buffalo.edu
128.205.7.57acsu.buffalo.edu
128.205.7.57acsu.buffalo.edu
128.205.7.57acsu.buffalo.edu
68.85.92.99 eye-catcher.com

When I run the following query, I get back 0 results -- anyone have any
thoughts? I am expecting to get back 3 records.

SELECT 
DISTINCT ClientIP, ClientDomain, COUNT(ClientDomain) AS
ClientDomainCount
FROM 
SMTPLog 
WHERE 
ClientDomainCount > 1 
GROUP BY 
ClientIP
ORDER BY 
ClientDomainCount DESC, ClientDomain ASC