On 2017/01/04 3:43 PM, Ken Wagner wrote:
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.
This is contrary to my understanding of the GLOB phrase (and I could
well be wrong about it). As I have it (and as is implemented by SQLite)
the GLOB operator implements a REGEXP that matches against a regexp
pattern such that the program '*[^1-9]*' expanded means:
* : ANY or none characters,
Followed by
[ : A character which is -
^ : NOT
1-9 : A character between 1 and 9
]
Followed by
* : ANY or none characters
In English one can simply ask: "Does it have zero or more characters of
any kind, followed by something that isn't a number 1 through 9, and
then again zero or more characters of any kind?"
This answer seems to me to be YES (i.e. MATCH) all of the following:
'A', 'ABC', 'AB6' or '5AB'
but not match:
'', '1979' or '5'
Sure, one can construe the value '5AB' to mean Any characters () + 5 +
Any characters (AB) to cause a non-match, but one can also construe the
term '5AB' to mean Any characters (5) + NOT a number (A) + Any
characters (B) and so it will match. Wildcards are tricky, and wildcards
implemented in the negative are even worse.
I personally agree with how it is done in off-the-shelf SQLite (though
my opinion is not important, what does the standard say? Is there a
standard?). If something else has a different implementation it is very
easy for any 3rd party item to override the GLOB and REGEXP functions to
a custom implementation, which is probably what you are seeing.
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?)
Because it may or may not override the GLOB and/or REGEXP functions.
SQLite claims to use the INFIX implementation of LIKE and GLOB natively.
Perhaps that is a starting point for research?
Hope that helps!
Ryan
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
