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&name=ext/icu for detai

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 sqlite-user

Re: [sqlite] like operator

On 12/5/2016 3:43 PM, Don V Nielsen wrote: Igor, I'm not sure if you gain anything from"length(lower(name))". Just "length(name)" would suffice. I'm guarding against various Unicode weirdnesses that could cause string length to change on case transformation. While SQLite only folds ASCII lett

Re: [sqlite] like operator

Igor, I'm not sure if you gain anything from"length(lower(name))". Just "length(name)" would suffice. On Mon, Dec 5, 2016 at 10:11 AM, Dominique Devienne wrote: > On Mon, Dec 5, 2016 at 4:24 PM, Igor Tandetnik wrote: > > > On 12/5/2016 10:19 AM, Igor Tandetnik wrote: > > > >> On 12/5/2016 7:30

Re: [sqlite] like operator

On Mon, Dec 5, 2016 at 4:24 PM, Igor Tandetnik wrote: > On 12/5/2016 10:19 AM, Igor Tandetnik wrote: > >> On 12/5/2016 7:30 AM, ravi.shan...@cellworksgroup.com wrote: >> >>> select name from employee table where name like '%Araya%' or name like >>> '%Amul%' or name like '%Aj%'; >>> >>> Table - Em

Re: [sqlite] like operator

On 12/5/2016 10:19 AM, Igor Tandetnik wrote: On 12/5/2016 7:30 AM, ravi.shan...@cellworksgroup.com wrote: select name from employee table where name like '%Araya%' or name like '%Amul%' or name like '%Aj%'; Table - Employee Id | Name | age | 1 | Arayan Kuma | 29 | 2 | Amul Kanth |

Re: [sqlite] like operator

On 12/5/2016 7:30 AM, ravi.shan...@cellworksgroup.com wrote: select name from employee table where name like '%Araya%' or name like '%Amul%' or name like '%Aj%'; Table - Employee Id | Name | age | 1 | Arayan Kuma | 29 | 2 | Amul Kanth | 30 | 3 | Ajay Kumar | 45 | I dont like

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 encounters this > >>

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 cut

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 quote(cast(char(

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

'10FFFD') -- 6 asql> select typeof('abc' || X'10FFFD'); typeof('abc' || X'10FFFD') -- text -Ursprüngliche Nachricht- Von: Dominique Devienne [mailto:ddevie...@gmail.com] Gesendet: Mittwoch, 14. Mai

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, 0x for binary / octa

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 collatio

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 co

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 decimal equivale

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 most collations e

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 in

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 part at the beginning​

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 p

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 provide this informatio

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 routines > which work out

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. I

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 differ

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 co

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 ch

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: http://sqlite.1065341.n5.nabble.com/LIKE-operator-and-collations-tp

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 x<'ab ​c​ ' ​ || ​ A

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 o

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 from the specific prob

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) by using the > highest

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 optimizati

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 :SearchString || '%'

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 the

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 use

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 > on the second column b

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 second is your text red

Re: [sqlite] like operator

On Thu, 9 Dec 2010 23:23:37 -0800 (PST), salmaan wrote: >Hi > >i need your help i want a query... > >i have a question for you suppose i have table name person in that column >name description which contains the below words > >The govt has asked airlines to issue an all-inclusive and reasonable f

Re: [sqlite] LIKE operator to support GREEK

Hi Here is the example I worked.. > Creating the table SqlLite.exe test.db "create table t3 (t3key INTEGER PRIMARY KEY,path TEXT,num double,timeEnter DATE , validate BOOL);" > Inserting into the table SqlLite.exe test.db "insert into t3 (path,num) values ('D:/CCVweiew/sampleexample/data/pat

Re: [sqlite] LIKE operator to support GREEK

Gerasimos Xydas <[EMAIL PROTECTED]> writes: > > Hello, > > I have built an SQLite 3 database from C code. > > CASE 1 > ... > CASE 2 > ... > > Best regards, > Gerasimos > Search the newsgroup ... start here http://thread.gmane.org/gmane.comp.db.sqlite.general/42112 Regards,

Re: [sqlite] LIKE operator and ATTACH databases memory usage

> Change cache sizes using separate cache_size > pragmas for each attached database. Thank you! (It would be nice if there is a hint for this behaviour in http://www.sqlite.org/lang_attach.html.) Is this correct? (At least it does not return an error) PRAGMA job01.cache_size=200 PRAGMA job02.ca

Re: [sqlite] LIKE operator and ATTACH databases memory usage

On Aug 18, 2008, at 8:34 AM, [EMAIL PROTECTED] wrote: > The example with just one main database does not consume more than > ~300KB > which seems to be pretty close to the specified 'cache_size'. The > second example with three attached database consumes > around 500KB per query and it looks

Re: [sqlite] LIKE operator and ATTACH databases memory usage

> Depending on what you are storing in fs_textid and what your LIKE > pattern is, you might get much better performance (and lower memory > usage) if you use GLOB instead of LIKE and if you explicitly code the > pattern rather than using the wildcard "?", and if you create a new > index: >

Re: [sqlite] LIKE operator and ATTACH databases memory usage

: "General Discussion of SQLite Database" Betreff: Re: [sqlite] LIKE operator and ATTACH databases memory usage On Aug 15, 2008, at 8:06 AM, [EMAIL PROTECTED] wrote: > Hello > > Why does SQLite consume 2.5MB memory every time when running a > statement on a attached database w

Re: [sqlite] LIKE operator and ATTACH databases memory usage

I'm going to check sqlite3_memory_used()/sqlite3_memory_highwater() next week as soon as I can. At the moment the following is clear: The application needs to run the same statement with LIKE operator for multiple attached databases. On the embedded side, the device crashes after a few statement

Re: [sqlite] LIKE operator and ATTACH databases memory usage

> This causes most of the database to be loaded into cache. Is there one cache per database connection or one cache per ATTACH'ed database? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LIKE operator and ATTACH databases memory usage

On Aug 15, 2008, at 8:06 AM, [EMAIL PROTECTED] wrote: > Hello > > Why does SQLite consume 2.5MB memory every time when running a > statement on a attached database with LIKE operator? > > Example 1: > SELECT fs_rec FROM fs_main WHERE fs_itemtype=? AND fs_textid > LIKE ?; // cons

Re: [sqlite] LIKE operator and ATTACH databases memory usage

On Aug 15, 2008, at 9:56 AM, [EMAIL PROTECTED] wrote: > > I don’t dare to use the term “leak” here. It is hard so say at the > moment where the memory is going to. At least, the > memory is not freed when sqlite3_finalize() is called on the > statement. Might it be possible, that this memory

Re: [sqlite] LIKE operator and ATTACH databases memory usage

I don’t dare to use the term “leak” here. It is hard so say at the moment where the memory is going to. At least, the memory is not freed when sqlite3_finalize() is called on the statement. Might it be possible, that this memory is allocated once per attached database and used for caching reas

Re: [sqlite] LIKE operator and ATTACH databases memory usage

On Aug 15, 2008, at 8:06 AM, [EMAIL PROTECTED] wrote: > Hello > > Why does SQLite consume 2.5MB memory every time when running a > statement on a attached database with LIKE operator? This mailing list strips off attachments. Please send the database by some other means. How do you know t

Re: [sqlite] Like operator

"Mahalakshmi.m" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Rowid Id Album > 1 4 > 2 3 > 3 2 > 4 1 > > SELECT rowid,Album FROM Temp WHERE Album like 'c%'; > Output: rowed -> 2 and Album -> > > My doubt is for this statement > "SELECT rowid , Album FROM Tem

Re: [sqlite] LIKE operator extremely slow? more than 10 times slower than egrep?

Thank you all for your input. -Spiros -- Image Video & Multimedia Systems Lab. Department of Electrical & Computer Eng. National Technical University of Athens http://www.image.ece.ntua.gr [EMAIL PROTECTED] wrote: Spiros Ioannou <[EMAIL PROTECTED]> wrote: John Stanton wrote: Sqlite does cater

Re: [sqlite] LIKE operator extremely slow? more than 10 times slower than egrep?

If performance is very important to you and you have a need for SQL elsewhere consider using the Sqlite virtual table interface. Your virtual table can be a flat file which is memory mapped and you use either a regex or fast string search on it. You will have a result in milliseconds. You co

Re: [sqlite] LIKE operator extremely slow? more than 10 times slower than egrep?

On 11/28/07, Spiros Ioannou <[EMAIL PROTECTED]> wrote: > > egrep gets to work with a flat text file, which it can easily read > > sequentially and get optimal performance from the OS's file > > buffering/cache management. It only needs to read a piece of the file > > and scan for patterns, repeat

Re: [sqlite] LIKE operator extremely slow? more than 10 times slower than egrep?

Spiros Ioannou <[EMAIL PROTECTED]> wrote: > John Stanton wrote: > > Sqlite does cater for text searching. Look at FTS. > > This is not text searching. No stemming, etc etc is required. Column has > exactly 1 word, and the 'LIKE' substring matching is performed at the > words' first characters (

Re: [sqlite] LIKE operator extremely slow? more than 10 times slower than egrep?

John Stanton wrote: Sqlite does cater for text searching. Look at FTS. This is not text searching. No stemming, etc etc is required. Column has exactly 1 word, and the 'LIKE' substring matching is performed at the words' first characters (not end-characters). Thanks, -Spiros --

Re: [sqlite] LIKE operator extremely slow? more than 10 times slower than egrep?

Trevor Talbot wrote: On 11/27/07, Spiros Ioannou <[EMAIL PROTECTED]> wrote: I had a 135MB, 1256132 lines, '@' separated text file containing various words and text fields (like a dictionary). Example record: [EMAIL PROTECTED]@[EMAIL PROTECTED],[EMAIL PROTECTED] altana : μικρός ανθόκηπος - εξώ

Re: [sqlite] LIKE operator extremely slow? more than 10 times slower than egrep?

Firstly, thank you all for your answers, egrep gets to work with a flat text file, which it can easily read sequentially and get optimal performance from the OS's file buffering/cache management. It only needs to read a piece of the file and scan for patterns, repeating until done. The only st

Re: [sqlite] LIKE operator extremely slow? more than 10 times slower than egrep?

You will always get much faster searching using a flat file and a grep-like search. For the search you quote you can do better than egrep with an integrated search algorithm like Boyer-Moore. This is no reflection on Sqlite, it is not intended to be a replacement for grep. Spiros Ioannou wr

Re: [sqlite] LIKE operator extremely slow? more than 10 times slower than egrep?

On 11/27/07, Spiros Ioannou <[EMAIL PROTECTED]> wrote: > I had a 135MB, 1256132 lines, '@' separated text file containing > various words and text fields (like a dictionary). > Example record: > [EMAIL PROTECTED]@[EMAIL PROTECTED],[EMAIL PROTECTED] altana : μικρός > ανθόκηπος - εξώστης, > ταράτσ

Re: [sqlite] LIKE operator syntax for white space

These only strip lines with Space characters. I need also to filter out also tabs, carriage returns and all series characters that constitute a white space. - To unsubscribe, send email to [EMAIL PROTECTED] ---

Re: [sqlite] LIKE operator syntax for white space

How do I filter out records that contain WHITE SPACE in a field or have this field empty? For example: select * from mytable where fld <> "" and fld LIKE . Do you mean "contain ONLY white space"? Are you after this? select * from mytable where trim(fld) <> "" ---

Re: [sqlite] like operator

The % is an escape character in the WWW form protocol. You need to interpret it and regard the next two bytes as a hex char. RaghavendraK 70574 wrote: Hi, we have given a web interface which receive delete request. Now in the req we get "%" and in the delete impl we do this delete from table

RE: [sqlite] like operator

I'm not sure I correctly understand your question, but: escaping the % in your query may be what you're looking for, i.e. delete from table where itemName like '\%.%' escape '\' Maybe. :) -Tom > -Original Message- > From: RaghavendraK 70574 [mailto:[EMAIL PROTECTED] > Sen

Re: [sqlite] like operator

You have to test your incoming values, and reject requests that have "%" (and other illegal) chars. I never allow real deletes from a web form, and especially not from trusted users. Consider adding a "deleted" column, and update the affected rows to indicate they've been deleted. It's a litt

Re: [sqlite] LIKE operator case-senisitive for international characters

On Tue, 2007-07-10 at 22:23 -0700, Sweden wrote: > I have searched many forums without success for the following question: > > Is there any possibility to use LIKE operator in a SELECT statment - > without being case sensitive AND with support for international characters? > I am using "PRAGMA ca

Re: [sqlite] LIKE operator with prepared statements

Eric Bohlman <[EMAIL PROTECTED]> wrote: > Dennis Cote wrote: > > You could also do this: > > SELECT x from y WHERE y.x LIKE '%' || ? || '%' ; > > > > The || operator concatenates the % characters with your string. Now you > > don't need to massage the string in the calling code. Six of one, hal

Re: [sqlite] LIKE operator with prepared statements

Dennis Cote wrote: You could also do this: SELECT x from y WHERE y.x LIKE '%' || ? || '%' ; The || operator concatenates the % characters with your string. Now you don't need to massage the string in the calling code. Six of one, half dozen of the other. Note, though, that as currently imp

Re: [sqlite] LIKE operator with prepared statements

Marian Olteanu wrote: But why don't you use SELECT x from y WHERE y.x LIKE ? ; and bind the first parameter to "%SomeText%" instead of "SomeText" like before? Chad, You could also do this: SELECT x from y WHERE y.x LIKE '%' || ? || '%' ; The || operator concatenates the % characters wi

RE: [sqlite] LIKE operator with prepared statements

But why don't you use SELECT x from y WHERE y.x LIKE ? ; and bind the first parameter to "%SomeText%" instead of "SomeText" like before? -Original Message- From: Slater, Chad [mailto:[EMAIL PROTECTED] Sent: Thursday, April 06, 2006 6:40 PM To: sqlite-users@sqlite.org Subject: [sqlite] L