Re: [sqlite] [SQLITE]ignoring syntax errors during search for sth include "symbols"
It will crash or exit the program. On Thu, Oct 18, 2018 at 2:27 PM Jens Alfke wrote: > > > > On Oct 18, 2018, at 11:17 AM, Maziar Parsijani < > maziar.parsij...@gmail.com> wrote: > > > > I just search for words an alphabets in different languages with python > and > > my database is sqlite but I need to do something to not getting error > when > > user input a wrong character like the ones that I told before.I can ban > > user to not input these characters but I am curious to find a way on > sqlite. > > What’s wrong with getting an error? If the error code or message is > specific enough, you can detect it and tell the user they’ve entered > invalid characters. > > —Jens > ___ > 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] [SQLITE]ignoring syntax errors during search for sth include "symbols"
hi, I just search for words an alphabets in different languages with python and my database is sqlite but I need to do something to not getting error when user input a wrong character like the ones that I told before.I can ban user to not input these characters but I am curious to find a way on sqlite. On Thu, Oct 18, 2018 at 8:25 AM Bob Gailer wrote: > On Oct 18, 2018 5:59 AM, "Maziar Parsijani" > wrote: > > > > Hi, > > how could I ignore syntax errors like this? > > > > > *SELECT *,* > > > > > > * highlight(searchsimpleenhanced, 2, '', '') text* > > > > > > * FROM searchsimpleenhanced* > > > > > > * WHERE searchsimpleenhanced MATCH 'sth][';* > > As I understand the documentation match must be defined someplace what is > the definition of match in your case? Or what are you trying to accomplish > by using match? > ___ > 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] [SQLITE]ignoring syntax errors during search for sth include "symbols"
Hi, how could I ignore syntax errors like this? > *SELECT *,* > > * highlight(searchsimpleenhanced, 2, '', '') text* > > * FROM searchsimpleenhanced* > > * WHERE searchsimpleenhanced MATCH 'sth][';* > there maybe nothing to match but I don't like to get syntax errors for a symbol or character like([ ] () , . @ ...) ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] [SQLITE] 3 questions.(tokenize in fts5)(number of highlights)(ignoring syntax errors for searching "symbols")
Hi, I have 3 questions on the below query: > SELECT searchsimpleenhanced.rowid, > > searchsimpleenhanced.*, > > fatrans.text AS trans, > > searchfast.text, > > highlight(fatrans, 0, '', '') mjp > > FROM searchsimpleenhanced > > JOIN > > fatrans ON fatrans.rowid = searchsimpleenhanced.rowid > > JOIN > > searchfast ON searchfast.rowid = fatrans.rowid > > WHERE fatrans MATCH 'فارسی' ; > First : How could I count the number of highlights?(not the number of rows) Second : if I have 2 columns with Arabic text like this : column 1 : *"الحمد لله رب العالمین "* column 2 : *"الْحَمْدُ لِلَّهِ رَبِّ الْعَالَمِينَ "* I want to know if there are any way to search for a match in column 1 for example "*رب*" but I can select the same match in column 2 without searching by its position in the string.Because in the both columns the " *رب*" is the third word and now I want to find it on column 1 and select the third word on column 2. Third : I want to know if there are anyway to get rid of syntax error during search for "?,[].."and such symbols. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS5 MATCH using "string*" matches just the exact string
Hi,Dan I have trimmed it here you can download it: https://drive.google.com/file/d/1D0xKF3DaOEbvfe9yFf0aFQvpo2nxWYqF/view?usp=sharing And the problem is searching with MATCH 'أعلم*' it must find 5 rows. it could be the same as I use LIKE "%أعلم%" On Tue, Sep 11, 2018 at 8:35 PM Dan Kennedy wrote: > On 09/11/2018 01:26 PM, Maziar Parsijani wrote: > > Thanks for your answer Eric,Sandu > > About the first idea of Eric I have to say its correct for me when I do a > > test with another fts5 table with no huge rows but my database has that > > problem.And about the other possibility I tested that before to set "*"at > > the first and end of the match but I have got an error.Here if there is > no > > problem I will attach an example Arabic text database if you search for > > 'أعلم*' it must find 83 rows but it will find 49 which is the same with > > "أعلم" results.and if you put "*"at the both sides you will get error.I > > don't know if I did something wrong during creation but this is all I > have > > got. > > The "*" may only occur following a string in an fts5 expression, not > before: > >https://sqlite.org/fts5.html#fts5_prefix_queries > > If possible, can you trim the database down to just two rows that > demonstrate the problem and upload it somewhere? i.e. so that it > contains two rows that should both be matched by 'أعلم*', but for which > the current FTS5 only matches one. > > Even if the complete database makes the problem obvious to Arabic > readers, it will be quite difficult for non-readers to deal with. > > Thanks, > Dan. > > > > > > > > > On Tue, Sep 11, 2018 at 10:20 AM Sandu Buraga > > wrote: > > > >> In Arabic you have a different direction of the text. Might be that the > >> reason? > >> > >> Sandu > >> > >> Eric Minbiole schrieb am Di., 11. Sep. 2018, > 00:23: > >> > >>>> SELECT rowid,text > >>>> FROM table > >>>> WHERE table MATCH 'أعلم*'; > >>>> > >>>> And I have to add this that my data is Arabic text. > >>>> > >>>> This method must find words that contains 'أعلم' but it doesn't. What > >>>> should I do now. > >>>> > >>>> > >>> I just tried this in SQLite version 3.24.0 on a Mac, and it seems to > >> work. > >>> Please see my example below. In particular, the first select doesn't > >> have a > >>> *, so it doesn't find the word. The second example includes a trailing > *, > >>> so it performs a "starts with" search, and does find the match. (Note > >> that > >>> I simply doubled the letters of your original arabic text to make a > >> longer > >>> word.) > >>> > >>> MacBook-II:Programs eric$ ./sqlite3 > >>>> SQLite version 3.24.0 2018-06-04 19:24:41 > >>>> Enter ".help" for usage hints. > >>>> Connected to a transient in-memory database. > >>>> Use ".open FILENAME" to reopen on a persistent database. > >>>> sqlite> CREATE VIRTUAL TABLE fts USING fts5(doc); > >>>> sqlite> INSERT INTO fts (rowid, doc) VALUES (1, 'english text'); > >>>> sqlite> INSERT INTO fts (rowid, doc) VALUES (2, 'arabic أعلمأعلم'); > >>>> sqlite> SELECT rowid FROM fts WHERE fts MATCH 'أعلم'; > >>>> sqlite> SELECT rowid FROM fts WHERE fts MATCH 'أعلم*'; > >>>> 2 > >>>> sqlite> > >>>> > >>> > >>> One possible explanation: You mentioned that you want a "contains" > >> search. > >>> However, the trailing asterisk in your example only results in a > "begins > >>> with" search. If you really want a "contains" search, you'll need to > put > >>> the * at both the beginning and end of the match word. E.g., "WHERE fts > >>> MATCH '*asdf*'; > >>> > >>> Hope this is helpful. > >>> > >>> ~Eric > >>> ___ > >>> 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 > > > > ___ > 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] [SQLITE]select from a table and use its data to select from another one
Hi, Really thanks to all with your complete answers. The best answer is from Keith Medcalf. On Sat, Sep 15, 2018 at 1:45 AM Rob Richardson wrote: > Double quotes can be used to specify that you mean a database object when > the name of the object might be confused with a keyword. For example, my > company's database models a production system with various recipes. We > call them "cycles". But the word "cycle" appears to have some specific > meaning inside SQL (or at least, inside PostgreSQL). So, although the > query >SELECT * FROM cycle > works, we should probably use > SELECT * FROM "cycle" > to avoid any possible ambiguity. > > RobR > > On Fri, Sep 14, 2018 at 2:12 PM Rob Richardson > wrote: > > > The use of single quotes instead of double quotes in database queries is > > not limited to SQLite. That's part of the SQL standard. > > > > RobR > > > > On Fri, Sep 14, 2018 at 2:05 PM David Raymond > > wrote: > > > >> Small typo: > >> > >> SELECT * FROM table2 JOIN table1 > >> ON table1.rowid = table2.rowid > >> WHERE table1.name LIKE '%smth%' > >> > >> > >> -Original Message- > >> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org > ] > >> On Behalf Of Simon Slavin > >> Sent: Friday, September 14, 2018 1:59 PM > >> To: SQLite mailing list > >> Subject: Re: [sqlite] [SQLITE]select from a table and use its data to > >> select from another one > >> > >> On 14 Sep 2018, at 6:50pm, Maziar Parsijani > > >> wrote: > >> > >> > I have 2 tables with the same rowid now I want to : > >> > select rowid from table1 where table1 like "%smth%" > >> > select * from table2 where rowid =(selected rows before) > >> > > >> > I mean if I could do it in a same query. > >> > >> This is what JOIN is for. > >> > >> SELECT * FROM table2 > >> JOIN table1.rowid = table2.rowid > >> WHERE table1.name LIKE '%smth%' > >> > >> Note that SQLite uses single quotes ' for text strings, not double > quotes > >> ". > >> > >> 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 > >> > > > ___ > 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] [SQLITE]select from a table and use its data to select from another one
Hi, Thanks for your answer.I used your answer like this : SELECT * FROM table2 JOIN table1 on table1.rowid = table2.rowid WHERE table1.name LIKE '%smth%' Because without the "table1 on" statement it didn't work . On Fri, Sep 14, 2018 at 10:29 PM Simon Slavin wrote: > On 14 Sep 2018, at 6:50pm, Maziar Parsijani > wrote: > > > I have 2 tables with the same rowid now I want to : > > select rowid from table1 where table1 like "%smth%" > > select * from table2 where rowid =(selected rows before) > > > > I mean if I could do it in a same query. > > This is what JOIN is for. > > SELECT * FROM table2 > JOIN table1.rowid = table2.rowid > WHERE table1.name LIKE '%smth%' > > Note that SQLite uses single quotes ' for text strings, not double quotes > ". > > 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
[sqlite] [SQLITE]select from a table and use its data to select from another one
Hi, I have 2 tables with the same rowid now I want to : select rowid from table1 where table1 like "%smth%" select * from table2 where rowid =(selected rows before) I mean if I could do it in a same query. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS5 MATCH using "string*" matches just the exact string
Thanks for your answer Eric,Sandu About the first idea of Eric I have to say its correct for me when I do a test with another fts5 table with no huge rows but my database has that problem.And about the other possibility I tested that before to set "*"at the first and end of the match but I have got an error.Here if there is no problem I will attach an example Arabic text database if you search for 'أعلم*' it must find 83 rows but it will find 49 which is the same with "أعلم" results.and if you put "*"at the both sides you will get error.I don't know if I did something wrong during creation but this is all I have got. On Tue, Sep 11, 2018 at 10:20 AM Sandu Buraga wrote: > In Arabic you have a different direction of the text. Might be that the > reason? > > Sandu > > Eric Minbiole schrieb am Di., 11. Sep. 2018, 00:23: > > > > SELECT rowid,text > > > FROM table > > > WHERE table MATCH 'أعلم*'; > > > > > > And I have to add this that my data is Arabic text. > > > > > > This method must find words that contains 'أعلم' but it doesn't. What > > > should I do now. > > > > > > > > I just tried this in SQLite version 3.24.0 on a Mac, and it seems to > work. > > Please see my example below. In particular, the first select doesn't > have a > > *, so it doesn't find the word. The second example includes a trailing *, > > so it performs a "starts with" search, and does find the match. (Note > that > > I simply doubled the letters of your original arabic text to make a > longer > > word.) > > > > MacBook-II:Programs eric$ ./sqlite3 > > > SQLite version 3.24.0 2018-06-04 19:24:41 > > > Enter ".help" for usage hints. > > > Connected to a transient in-memory database. > > > Use ".open FILENAME" to reopen on a persistent database. > > > sqlite> CREATE VIRTUAL TABLE fts USING fts5(doc); > > > sqlite> INSERT INTO fts (rowid, doc) VALUES (1, 'english text'); > > > sqlite> INSERT INTO fts (rowid, doc) VALUES (2, 'arabic أعلمأعلم'); > > > sqlite> SELECT rowid FROM fts WHERE fts MATCH 'أعلم'; > > > sqlite> SELECT rowid FROM fts WHERE fts MATCH 'أعلم*'; > > > 2 > > > sqlite> > > > > > > > One possible explanation: You mentioned that you want a "contains" > search. > > However, the trailing asterisk in your example only results in a "begins > > with" search. If you really want a "contains" search, you'll need to put > > the * at both the beginning and end of the match word. E.g., "WHERE fts > > MATCH '*asdf*'; > > > > Hope this is helpful. > > > > ~Eric > > ___ > > 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
[sqlite] FTS5 MATCH using "string*" matches just the exact string
Hi I am new here and I am not an expert on sqlite my question is about match option that I have read from https://sqlite.org/fts5.html but the problem is when I want to not find the exact word so I use : SELECT rowid,text FROM table WHERE table MATCH 'أعلم*'; And I have to add this that my data is Arabic text. This method must find words that contains 'أعلم' but it doesn't. What should I do now. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
