Yes, I changed the query to NOT GLOB '*[1-9]*' and then it omitted the
1-9 char-containing entries.
However the logic of 'zero or any chars, then any single char NOT 1 thru
9, then zero or any chars' should OMIT any name with a 1 thru 9 in it
regardless if it is 'Vol. 3', for example, or 'Vol. 33' because as soon
as any single 1 thru 9 char is encountered the expression is false,
i.e., it contains at least one char of 1 thru 9.
There is yet another product "DB Browser for SQLite" using SQLite v
3.9.2. It, too, omits any row where name contains any char 1 thru 9. It
appears SQLite at one point did this as 'GLOB '*[^1-9]*'.
But it does not do so now. Does SQLite3 provide a detailed syntax
description of the GLOB permutations honored (and, perhaps, those
deprecated?)
Thanks.
Ken Wagner
On 01/04/2017 07:13 AM, R Smith wrote:
Just one more point of clarity, in case my previous explanation did
not high-light this:
The query:
select trackid, name from tracks where name GLOB '*[^1-9]*';
is completely different to the query:
select trackid, name from tracks where name NOT GLOB '*[1-9]*';
The prior excludes only names which are completely non-zero-containing
integers (such as "1979"), the latter excludes any value that contains
a character between 1 and 9 at any position (such as "Opus No. 1" or
"20 Flight Rock"). The two queries merely intersect in one specific
point in the Chinook DB (at "1979", that is) but they have nothing
else in common.
Cheers,
Ryan
On 2017/01/04 2:53 PM, R Smith wrote:
On 2017/01/04 7:17 AM, Ken Wagner wrote:
About 2/3 the way down the page at:
http://www.sqlitetutorial.net/sqlite-glob/ Get names without
[1-9].
select trackid, name from tracks where name GLOB '*[^1-9]*';
Works properly in SQLiteMan and the SQLite Tutorial. ( Two
different products, not officially part of sqlite.org, I think, but
use the sqlite app.)
But not in sqlite3 3.15.1 and 3.16.1.
That should not "work" in ANY version of SQLite (depending on what
you mean by "work"). The phrase '*[^1-9]*' essentially asks to match
'Anything, followed by something that is NOT a character 1 through 9,
followed by Anything again' which is really ANYTHING that isn't
specifically an integer which doesn't contain zeroes. i.e. it will
exclude a name like "1996" but not a name like "2016" or "123.456",
so in the Chinook database (which the tutorial uses) the query will
list ALL thousands of names except one (namely a song titled: "1979")
so it would be hard for you to differentiate between the query that
"works" and one that doesn't.
Perhaps suggest to the Tutorial creators to use phrases like
'*[^0-9]*' in stead of 1-9 to start with, and then maybe an example
that has more than one single exclusion across the entire DB (which
make the results seem superfluous and not very educational).
Another confusing bit is your claim that it "works" in an earlier
version and not in the newer versions - I get the same results in 3.9
and 3.16.
May I ask how it differs for you?
_______________________________________________
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
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users