Re: [sqlite] LIKE operator and collations

Look into using the ICU extension. If you're compiling sqlite yourself, just define SQLITE_ENABLE_ICU to 1 (And link with the ICU libraries), otherwise you'll have to grab the source and compile it as a loadable module. https://www3.sqlite.org/cgi/src/dir?ci=03c4f00317233a34=ext/icu for details.

Re: [sqlite] LIKE operator and collations

You can write your own LIKE function and use that: If you have the source for "collate TURKISH_CI" then you might be able to use it in your own function. Simon. ___ sqlite-users mailing list

Re: [sqlite] LIKE operator and collations

On Wed, May 14, 2014 at 1:35 PM, Jan Slodicka wrote: > Simon Slavin-3 wrote > > On 13 May 2014, at 5:21pm, Constantine Yannakopoulos wrote: > > > >> ​This is very interesting Jan. The only way this could fail is if the > >> collation implementation does something funny if it

Re: [sqlite] LIKE operator and collations

Simon Slavin-3 wrote > On 13 May 2014, at 5:21pm, Constantine Yannakopoulos wrote: > >> ​This is very interesting Jan. The only way this could fail is if the >> collation implementation does something funny if it encounters this >> character​, e.g. choose to ignore it when comparing. > > That

Re: [sqlite] LIKE operator and collations

Hick Gunter wrote: > I was under the impression you wanted to achieve this: > > > select hex('abc' || X'10FFFD'); > 61626310FFFD > > select length('abc' || X'10FFFD'); > 6 If you want to create characters through a blob, you have to use the correct UTF-8 encoding: sqlite> select

Re: [sqlite] LIKE operator and collations

Dominique Devienne wrote > On Tue, May 13, 2014 at 5:50 PM, Jan Slodicka > jano@ > wrote: >> So one could replace "LIKE 'xxx%'" by "BETWEEN('xxx', 'xxx' + >> '\uDBFF\uDFFD'). > > make that > > BETWEEN('xxx', 'xxx' + char(1114109)) > > I don't think SQlite supports \u literals, nor does

Re: [sqlite] LIKE operator and collations

X'10FFFD'); typeof('abc' || X'10FFFD') -- text -Ursprüngliche Nachricht- Von: Dominique Devienne [mailto:ddevie...@gmail.com] Gesendet: Mittwoch, 14. Mai 2014 09:28 An: General Discussion of SQLite Database Betreff: Re: [sqlite] LIKE operator and collations On Wed, May

Re: [sqlite] LIKE operator and collations

On Wed, May 14, 2014 at 8:30 AM, Hick Gunter wrote: > Actually SQLite does support X'...' literals for creating blobs. Note sure how that's relevant Hick. We don't need a blob, but a integer for char(). I was obviously talking about *number* literals (prefixed with 0b, 0,

Re: [sqlite] LIKE operator and collations

Actually SQLite does support X'...' literals for creating blobs. -Ursprüngliche Nachricht- Von: Dominique Devienne [mailto:ddevie...@gmail.com] Gesendet: Dienstag, 13. Mai 2014 18:19 An: General Discussion of SQLite Database Betreff: Re: [sqlite] LIKE operator and collations On Tue

Re: [sqlite] LIKE operator and collations

On 13 May 2014, at 5:21pm, Constantine Yannakopoulos wrote: > ​This is very interesting Jan. The only way this could fail is if the > collation implementation does something funny if it encounters this > character​, e.g. choose to ignore it when comparing. That cuts out a very large number of

Re: [sqlite] LIKE operator and collations

On Tue, May 13, 2014 at 5:50 PM, Jan Slodicka wrote: > So one could replace "LIKE 'xxx%'" by "BETWEEN('xxx', 'xxx' + '\uDBFF\uDFFD'). make that BETWEEN('xxx', 'xxx' + char(1114109)) I don't think SQlite supports \u literals, nor does it support hex literals, so must use the

Re: [sqlite] LIKE operator and collations

On Tue, May 13, 2014 at 6:50 PM, Jan Slodicka wrote: > Any comments are welcome. ​This is very interesting Jan. The only way this could fail is if the collation implementation does something funny if it encounters this character​, e.g. choose to ignore it when comparing. Since

Re: [sqlite] LIKE operator and collations

Constantine Yannakopoulos wrote > On Mon, May 12, 2014 at 4:46 PM, Jan Slodicka > jano@ > wrote: > I understand that it is difficult to find the least greater character of a > given character if you are unaware of the inner workings of a collation, > but maybe finding a consistent upper limit

Re: [sqlite] LIKE operator and collations

> ​It can be implemented if the definition of a collation is extended to be able to provide this information as Simon suggested. Sure, this could be done. But I am not sure whether it would be that usefull. For example I would not use it for my current application (C#). -- View this message

Re: [sqlite] LIKE operator and collations

On Mon, May 12, 2014 at 6:45 PM, Simon Slavin wrote: > They're not the same, Jan. This optimization is for one very specific use > of LIKE: where the match string has no wildcards except for a percent sign > at the end In theory a LIKE can be optimized if it has a fixed

Re: [sqlite] LIKE operator and collations

On 12 May 2014, at 4:05pm, Jan Slodicka wrote: > And whether we'll call it "LIKE optimization" or "using BETWEEN", the > problem remains basically the same. They're not the same, Jan. This optimization is for one very specific use of LIKE: where the match string has no wildcards except for a

Re: [sqlite] LIKE operator and collations

On Mon, May 12, 2014 at 5:41 PM, Jan Slodicka wrote: > I think that the answer is as before: The LIKE optimization cannot be > performed by the DB engine, but only by the collation author. > ​It can be implemented if the definition of a collation is extended to be able to

Re: [sqlite] LIKE operator and collations

On Mon, May 12, 2014 at 5:22 PM, Simon Slavin wrote: > agree: it's not possible to deduce an optimization without understanding > the collation. It might be possible to introduce it in a future version of > SQLite by requiring anyone who writes an optimization to supply

Re: [sqlite] LIKE operator and collations

> However, I don't think it's necessary to solve this problem. Just don't try to optimize it. Whoever is doing the programming knows that pattern matching is slow. If they want a fast solution they'll use BETWEEN instead. And that will make them have to provide their own comparison strings.

Re: [sqlite] LIKE operator and collations

> ​I understand that it is difficult to find the least greater character of a given character If you understand this, then you must admit that it cannot be done by the DB engine. Secondly: I pointed out to the problems when deriving the string "abd" from "abc". However, you suggest now a

Re: [sqlite] LIKE operator and collations

On 12 May 2014, at 3:05pm, Constantine Yannakopoulos wrote: > ​I understand that it is difficult to find the least greater character of a > given character if you are unaware of the inner workings of a collation, > but maybe finding a consistent upper limit for all characters in all > possible

Re: [sqlite] LIKE operator and collations

On Mon, May 12, 2014 at 4:05 PM, Constantine Yannakopoulos < alfasud...@gmail.com> wrote: > ​I understand that it is difficult to find the least greater character of a > given character if you are unaware of the inner workings of a collation, > but maybe finding a consistent upper limit for all

Re: [sqlite] LIKE operator and collations

Hi Clemens, I know that link very well. I answered Constantine's statement who claimed that Sqlite implementation could be less restrictive. I just tried to explain why it is not possible. Jan -- View this message in context:

Re: [sqlite] LIKE operator and collations

On Mon, May 12, 2014 at 4:46 PM, Jan Slodicka wrote: > Sqlite LIKE optimization is described as follows: > A like pattern of the form "x LIKE 'abc%'" is changed into constraints > "x>='abc' AND x<'abd' AND x LIKE 'abc%'" > Actually, I would do something ​like: "x>='abc' AND

Re: [sqlite] LIKE operator and collations

Jan Slodicka wrote: > Sqlite LIKE optimization is described as follows: > A like pattern of the form "x LIKE 'abc%'" is changed into constraints > "x>='abc' AND x<'abd' AND x LIKE 'abc%'" > > If you look into sqlite code, then the string "abd" is generated from "abc" > using a trivial algebra. > >

Re: [sqlite] LIKE operator and collations

Sqlite LIKE optimization is described as follows: A like pattern of the form "x LIKE 'abc%'" is changed into constraints "x>='abc' AND x<'abd' AND x LIKE 'abc%'" If you look into sqlite code, then the string "abd" is generated from "abc" using a trivial algebra. However, this algebra won't work

Re: [sqlite] LIKE operator and collations

On Sun, Feb 9, 2014 at 8:27 PM, Simon Slavin wrote: > I know it's a hack. But it's an elegant efficient hack that takes > advantage of the things SQLite does well. As long as that's the only way > you were using LIKE. > Don't get me wrong, the solution is good. But apart

Re: [sqlite] LIKE operator and collations

On 9 Feb 2014, at 6:05pm, Constantine Yannakopoulos wrote: > - You are assuming that 'z' is the higher order character that can appear > in a value. This is not the case; for instance greek characters have higher > order than 'z'. This can be fixed (only for latin/greek)

Re: [sqlite] LIKE operator and collations

Thank you all for your replies, @RSmith: My question is about the _specific_ case: SELECT * FROM ATable WHERE AColumn LIKE 'FixedSearchPart' || 'SomeWildCard' where the right side of the LIKE operator is a constant that has a fixed part at the beginning and a wildcard after that. The

Re: [sqlite] LIKE operator and collations

On 9 Feb 2014, at 4:21pm, Yuriy Kaminskiy wrote: > Unfortunately, builtin LIKE ignores collation, and if you override LIKE (as in > ICU extension), sqlite won't use index for optimization. However, this particular use of LIKE ... > SELECT * FROM ATable WHERE AColumn LIKE

Re: [sqlite] LIKE operator and collations

Constantine Yannakopoulos wrote: > I have a case where the user needs to perform a search in a text column of > a table with many rows. Typically the user enters the first n matching > characters as a search string and the application issues a SELECT statement > that uses the LIKE operator with

Re: [sqlite] LIKE operator and collations

On 2014/02/09 13:18, Constantine Yannakopoulos wrote: Hello all, I have a case where the user needs to perform a search in a text column of a table with many rows. Typically the user enters the first n matching characters as a search string and the application issues a SELECT statement that

Re: [sqlite] LIKE operator and collations

On Sun, Feb 9, 2014 at 1:25 PM, Simon Slavin wrote: > store two text columns in the table. The first is the text as entered. > The second is your text reduced to its simplified searchable form, > probably all LATIN characters, perhaps using some sort of soundex. Search >

Re: [sqlite] LIKE operator and collations

On 9 Feb 2014, at 11:18am, Constantine Yannakopoulos wrote: > So, a full table scan seems inevitable. I can't answer the specific question you asked, but I have a suggestion for your program: store two text columns in the table. The first is the text as entered. The