[sqlite] regex -- no clue

Hi all, I read in the documentation "The REGEXP operator is a special syntax for the regexp() user function. No regexp() user function is defined by default and so use of the REGEXP operator will normally result in an error message. If a application-defined SQL function

[sqlite] import thousands of documents in SQLite

All, We need to import thousands of documents in an SQLite db for use with FTS. The FTS part I understand (more or less) but I don't know how to import so many docs in the DB. Does anyone know a tool to do this? I won't be the one doing the import, but users that can't work on command line (or we

[sqlite] read full txt file in one record

Dear all, what would be the best way to read a full txt file into one record? Now, when I use .import FILE TABLE I have every line on a record. I need all lines of one file on one record. The next record I need to fill with all lines of another file. thanks for your advise. Gert __

[sqlite] FTS snippet()

Hi all, I'm sure I'm doing something stupid here... CREATE VIRTUAL TABLE example USING fts4(TOKEN, CONTEXT); INSERT INTO example(TOKEN, CONTEXT) VALUES('one', 'This is just one sentence.'); INSERT INTO example(TOKEN, CONTEXT) VALUES('two', 'This is just one sentence. Sorry, it

Re: [sqlite] FTS snippet()

Drake, if I do this, I get: SQL logic error or missing database. Thanks Gert ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] LIMIT

All, I'm sure it must be possible, I just don't find how. I have a table T with 2 fields (F1 and F2). The F1 are unique and the F2 are not unique. I would like to get only 10 F1 fields for each unique F2. LIMIT 10 is the easy part... But how do I express the query so it F1 is LIMITed to 10 for e

Re: [sqlite] LIMIT

mith : > > On 2018/06/22 10:04 PM, Gert Van Assche wrote: > > All, > > > > I'm sure it must be possible, I just don't find how. > > I have a table T with 2 fields (F1 and F2). The F1 are unique and the F2 > > are not unique. > > I would like to g

Re: [sqlite] LIMIT

r might break that because without the > order by the results of the inner select are free to include a different 10 > rowids for every value in the outer query) > > > On 23 Jun 2018, at 9:50 pm, Gert Van Assche wrote: > > > > Hi Ryan, thanks for this. This is work

Re: [sqlite] LIMIT

Ryan, my dataset isn't that big. 11K records. Your solution is noticeable faster 996 ms vs 13126 ms. Interesting! gert Op za 23 jun. 2018 om 18:09 schreef R Smith : > > On 2018/06/23 2:47 PM, Gert Van Assche wrote: > > Barry, that's even easier indeed. And it works p

Re: [sqlite] LIMIT

> On 2018/06/22 10:04 PM, Gert Van Assche wrote: > >>> All, > >>> > >>> I'm sure it must be possible, I just don't find how. > >>> I have a table T with 2 fields (F1 and F2). The F1 are unique and > >>> the F2 > >>

Re: [sqlite] LIMIT

ique later. But I like to stick to SQLite. Gert Op ma 25 jun. 2018 om 01:00 schreef R Smith : > > On 2018/06/24 9:30 PM, Gert Van Assche wrote: > > whaw... I could never come up with solutions like this. Will this work > with > > SQLite? > > Where can I read more about

Re: [sqlite] LIMIT

Thanks Olivier, very good to know. gert Op di 26 jun. 2018 om 09:06 schreef Olivier Mascia : > > Le 26 juin 2018 à 07:56, Gert Van Assche a écrit : > > > > If I would like to use these ranking techniques in SQLite, can I do this > > with an extension? Has nobody cre

[sqlite] importing a large TSV file

Hi all, I need to create an SQLite db from a large TSV file. (30 GB) Are there any setting I can give to the db so I can speed up the import? thank you Gert ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.o

Re: [sqlite] importing a large TSV file

Thank you all for these tips. Very helpful! Op di 2 apr. 2019 om 08:35 schreef Rowan Worth : > On Mon, 1 Apr 2019 at 19:20, Domingo Alvarez Duarte > wrote: > > > Hello Gert ! > > > > I normally do this (be aware that if there is a power outage the > > database is screwed): > > > > === > > > > PR

[sqlite] SQLite using internally by Windows 10

Great! Congrats! 2015-05-01 5:24 GMT+02:00 Richard Hipp : > https://twitter.com/john_lam/status/593837681945092096 > > -- > D. Richard Hipp > drh at sqlite.org > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailingl

[sqlite] Update with out without WHERE?

Hi all, When I do an update in a huge table, should it write it like this UPDATE T1 SET F1=replace(F1, 'x', 'y') WHERE F1 LIKE '%x%'; or without where clause like this UPDATE T1 SET F1=replace(F1, 'x', 'y'); T1 has an index on F1, which is a TEXT field. thanks gert

[sqlite] Update with out without WHERE?

Thank you both for the discussion and conclusion. 2015-05-24 18:32 GMT+02:00 Simon Slavin : > > On 24 May 2015, at 4:58pm, Keith Medcalf wrote: > > > This is not possible and would make an update operation incredibly > slow. What you mean is that a row which is not updated is not updated. A >

[sqlite] SQLite Extensions

Thanks for sharing this, Keith! 2015-11-20 6:42 GMT+01:00 Keith Medcalf : > > For anyone who is interested, I have compiled for 32-bit Windows all the > extensions that are included in the SQLite3 distribution /ext/misc > directory using MinGW (gcc 4.8.1) and have added a few others I have > writ

[sqlite] SQLite Extensions

Thanks for sharing this, Keith! 2015-11-20 6:42 GMT+01:00 Keith Medcalf : > > For anyone who is interested, I have compiled for 32-bit Windows all the > extensions that are included in the SQLite3 distribution /ext/misc > directory using MinGW (gcc 4.8.1) and have added a few others I have > writ

[sqlite] The Simplest SQLite Common Table Expression Tutorial

You're a great teacher, David. Thanks for sharing this! gert 2015-09-25 22:17 GMT+02:00 David Barrett : > Hey all, just wanted to share this in case anybody is also looking for a > very simple tutorial for CTE's in sqlite: > > > http://blog.expensify.com/2015/09/25/the-simplest-sqlite-common-tab

[sqlite] Is there something like PHPAdmin for SQLite

I'm using https://www.phpliteadmin.org/ for this. Great tool and cheap: free! 2016-04-20 18:12 GMT+02:00 Cecil Westerhof : > I am blowing the horn for SQLite. ;-) A colleague of mine likes it in > principal, but he provides web hosting with a database. He has to provide > his clients with a web

[sqlite] SQLite workshop

Cecil, I like the idea but I can't give an outline. It will depend on your audience. Tables, Views, Indexes, Triggers, Joins... One piece of advice I can give you: use an example anyone can understand. I once have a db training and I got lost because the example was about football and the only th

Re: [sqlite] feature req: PLEASE why the heck COMMENT fields are not supporte in years!!

McKAY Gerardo (PICCORO) > http://qgqlochekone.blogspot.com > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- -- -- -- -- -- -- -- -- -- --

[sqlite] on insert new row, increment date with a month

All, Does anyone know if it is possible for a date field to be automatically incremented with a month when a new record is created? If the last row contains "2013-01-01" in the DateField then the DateField of the new row should automaticllay be "2013-02-01". Thanks gert

Re: [sqlite] on insert new row, increment date with a month

t of month','+1 month') WHERE nDate IS NULL; END thanks all for your help, Gert 2014-02-11 19:12 GMT+01:00 Petite Abeille : > > On Feb 11, 2014, at 7:07 PM, Gert Van Assche wrote: > > > Does anyone know if it is possible for a date field to be automatically

[sqlite] importing CSV with a random empty line at the end

All, I received 100.000 UTF-8 files (average size 50kb) "ready for import" in an SQLite db. 90% of them go fine, but some files have an empty line at the very end of the fine (so an extra EOL before the EOF). Of course, the import fails... Is there an easy way to get rid of that extra empty line

Re: [sqlite] importing CSV with a random empty line at the end

ty.csv test > empty.csv:2: expected 2 columns but found 1 - filling the rest with NULL > sqlite> select * from test; > 1|test > |test > > As the bindings are not cleared, it is not a null value but the > previous bound value which is inserted. > Regards. > > On Mon,

[sqlite] error "malformed database schema" on view in attached table

All, I get this error message: malformed database schema (WrongTargetLang) - view [WrongTargetLang] cannot reference objects in database main when I try to attach a database containing a view. If I delete the view, I get this message for another view. I get that message for all views. I can ope

Re: [sqlite] error "malformed database schema" on view in attached table

Bingo! That was indeed the problem, Kevin. I'm sorry I did not find that user group posting. Thanks. 2014-07-01 11:30 GMT+02:00 Kevin Benson : > On Tue, Jul 1, 2014 at 5:07 AM, Gert Van Assche wrote: > > > All, > > > > I get this error message: >

[sqlite] Importing a lot of data from many databases

All, I'm using the windows shell and I have to import one table from 50.000 small sqlite files into one big sqlite file. These are the 2 options I see: 1 - Open the BIG db, attach all small files, ten by 10, and copy the tables from the attached databases to the big table. I can speed up the imp

[sqlite] 64-bit precompiled command-line shell binary

All, where could I download a 64-bit sqlite3.exe for running on Windows 8? Thanks, Gert ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Importing a lot of data from many databases

Thanks Simon, this is exactly what I needed to know. gert 2014-07-01 16:48 GMT+02:00 Simon Slavin : > > > On 1 Jul 2014, at 12:26pm, Gert Van Assche wrote: > > > > 1 - Open the BIG db, attach all small files, ten by 10, and copy the > tables > > from the attached

Re: [sqlite] 64-bit precompiled command-line shell binary

Maybe a stupid question: how should I use this DLL? just put in the same folder as the sqlite3.exe? 2014-07-01 16:39 GMT+02:00 RSmith : > > On 2014/07/01 14:29, Gert Van Assche wrote: > >> All, >> >> where could I download a 64-bit sqlite3.exe for running on Window

Re: [sqlite] 64-bit precompiled command-line shell binary

Thanks Jan. I'll test it this afternoon. gert 2014-07-03 13:55 GMT+02:00 Jan Nijtmans : > 2014-07-01 21:12 GMT+02:00 RSmith : > > I seem to remember a similar question having surfaced some time ago but > > cannot find the reference now - so let me ask the great forum devs again: > > WIll the 64

Re: [sqlite] 64-bit precompiled command-line shell binary

Jan, for what I want to do, this exe is a perfect solution. It is working fine. I cannot test what RSmith wants to test, but I trust his expertise on this (SQLitespeed is a nice tool!) Thanks for your work on this, both of you. gert 2014-07-03 15:54 GMT+02:00 RSmith : > > On 2014/07/03 13:

Re: [sqlite] XML into sqlite

I'm using XSLT to create CSV. gert 2014-09-11 7:04 GMT+02:00 Carlos A. Gorricho : > > How would you recommend to drop XML data into an sqlite DB? I am guessing > there is more than one way. > > I thought of importing XML into Excel, converting to csv, for further > import into sqlite, now that I

Re: [sqlite] A new extension for sqlite to analyze the string metrics

Andea, Seems like a very interesting extension to me. I cannot make it run on Windows. I get error code 0xc00012f. Any idea why this is ? thanks gert 2014-09-25 10:11 GMT+02:00 Andrea Peri : > Hi, > for who eventually interested. > > Fr a specific internal use I develope a new simple extensio

Re: [sqlite] A new extension for sqlite to analyze the stringmetrics

All this is purely a guess, and could easily be wrong; I > > can't take a peek > > at the lib myself right now; but I mention this now on the > > chance that it > > saves some time debugging. > > > > -dave > > > > > > > -Original Mess

Re: [sqlite] A new extension for sqlite to analyze the stringmetrics

Andrea, where do I find it? thanks gert 2014-09-27 13:48 GMT+02:00 Andrea Peri : > Hi, > I commit the new extension with the runtime statically liked. > I test it on a clean machine and seem work. > > The extension is compiled on 32bit. > So need a sqlite 32bit to work. > > I guess it should no

[sqlite] FTS4 languageid : not sure I understand this correctly

All, I have the feeling this is the most stupid question ever, but... If I create a FTS4 table, put text in it, could I use the languageid to figure out what Language that text actually is? Is that how langID works? I did some tests, but the LangID seems to be 0 all the time, so or I'm doing somet

Re: [sqlite] FTS4 languageid : not sure I understand this correctly

;s designed to prevent cross-lang contamination in the index. > > -Original Message- > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Gert Van Assche > Sent: Thursday, January 31, 2013 2:19 PM > To: sqlite-users > Subject:

Re: [sqlite] FTS4 languageid : not sure I understand this correctly

:27 PM, Gert Van Assche wrote: > > > Thanks Michael. Not what I hoped for but now I understand it. > > Perhaps of interest: > > Language Identification Tools > http://www.let.rug.nl/~vannoord/TextCat/competitors.html > > ___

[sqlite] import TXT file

All, I have to import text files into an SQLite table. The txt files are plain text in UTF-8. They don't contain tabs and the line ending is CRLF. One line in the TXT should become one record in the table. CREATE TABLE Source (Segments); .import 'test.txt ' Sour

Re: [sqlite] import TXT file

Binns > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > On 21/02/13 02:34, Gert Van Assche wrote: > > Does anyone see something obvious I'm doing wrong? > > There is no need to bash the SQLite shell into what you are trying to do. > You can write your

Re: [sqlite] import TXT file

Thanks Igor. I will look for that. 2013/2/21 Igor Tandetnik > On 2/21/2013 5:34 AM, Gert Van Assche wrote: > >> I have to import text files into an SQLite table. >> The txt files are plain text in UTF-8. They don't contain tabs and the >> line >> ending is CRL

Re: [sqlite] import TXT file

Mihael, I'll look for a developer who can compile the code for me. I'm not a developer. If I understand correctly, you wrote a tool to do a CSV import in an SQLite db in a much more controlled way, and if the import fails, we'll know at least why. With the code like it is now, can I use it on pla

Re: [sqlite] import TXT file

If I remove the double quotes, the problem is solved indeed. I'm working on windows. Do you know if there is an alternative to the SED command or tool? Or is there a way I can force sqlite3 to ignore these double quotes? thanks gert 2013/2/21 雷钦 > On 2013-02-21 13:15:16 +0100,

Re: [sqlite] import TXT file

found a good tool: http://findandreplace.codeplex.com/documentation problem solved... I think. thanks all for your help. 2013/2/21 Gert Van Assche > If I remove the double quotes, the problem is solved indeed. > I'm working on windows. Do you know if there is an alternative

Re: [sqlite] import TXT file

Thanks Kees. 2013/2/21 Kees Nuyt > On Thu, 21 Feb 2013 18:00:58 +0100, Gert Van Assche > wrote: > > >If I remove the double quotes, the problem is solved indeed. > >I'm working on windows. Do you know if there is an alternative to the SED > >command or tool? >

[sqlite] update the same record in multiple segments at once

All, I don't know if this is possible. A segment in table "Files" contains a record "Content" with a fields like this "XXX " Needs to become "XXX 123 456 78 90" Based on a "Translation" table were I have |123 |456 |78 |90 If I do UPDATE [F

Re: [sqlite] update the same record in multiple segments at once

t FROM SPLIT WHERE delimiter = ' ' and record = ; > > CREATE VIRTUAL TABLE split USING split(''); > > Write a function "JOIN" that will return a string concatenation of its > parameters > > JOIN(,...) > > The result will be sort of SQL... >

[sqlite] Break on cumulative sum

All, I don't know how to achieve this: I need to put the cumulative sum in a field, and create a group as soon as that cumulative sum is over a breakpoint value (10). This is an example table: CREATE TABLE [Test] (TextField, ValueField, CumulativeValue, BreakPoint DEFAULT 10, GroupName); insert

Re: [sqlite] Break on cumulative sum

); > insert into [Test] values('J', '8', null, '10',(select GroupName from > MyGroup)); > insert into [Test] values('K', '2', null, '10',(select GroupName from > MyGroup)); > select * from [Test]; > A|2|2|10|1 > B|3|5|1

Re: [sqlite] Break on cumulative sum

Mar 2013 14:46:40 +0100 > Gert Van Assche wrote: > > > All, I don't know how to achieve this: I need to put the cumulative > > sum in a field, and create a group as soon as that cumulative sum is > > over a breakpoint value (10). > > This was a bit of a challenge b

Re: [sqlite] SQLite3

Matt, it would be wise if you use a tool like the SQLite Expert; that will generate statements that you can use on command line as well. gert 2013/3/5 Duncan, Matthew > I am hoping you can help me. > > I have a file "DB2.db" in sqlite3 format. I am trying to view the > database using the

[sqlite] looking up records with terms in a table

All, I don't know if I can do this. I have a table T1 with Sentences and a table T2 with Terms. If I want to find all Sentences with the term "GM" I search like this: SELECT [Sentences] FROM [T1] WHERE [Sentences] LIKE '%GM%'; What I would like to do is look for all terms that appear in the Term

Re: [sqlite] looking up records with terms in a table

Clemens, doesn't seem to work... The terms are just a part of the sentence, not a full match. Your query does find full matches. thanks gert 2013/3/26 Clemens Ladisch : > Gert Van Assche wrote: >> What I would like to do is look for all terms that appear in the Terms table. >

Re: [sqlite] looking up records with terms in a table

From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Gert Van Assche > Sent: Tuesday, March 26, 2013 12:53 PM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] looking up records with terms in a table > > Clemens, doesn't seem

Re: [sqlite] looking up records with terms in a table

Bingo! Thanks you all! 2013/3/26 Clemens Ladisch : > Gert Van Assche wrote: >> 2013/3/26 Clemens Ladisch : >>> Gert Van Assche wrote: >>>> What I would like to do is look for all terms that appear in the Terms >>>> table. >>>> Something lik

Re: [sqlite] looking up records with terms in a table

Nice 2013/3/26 Petite Abeille : > > On Mar 26, 2013, at 10:36 PM, "Paul Mathieu" wrote: > >> SELECT Sentences FROM T1 JOIN T2 ON T1.Sentences LIKE >> CONCAT('%',T2.Terms,'%') > > Alternatively, use FTS [1]: > > sqlite> create virtual table sentence using fts4( content text ); > sqlite> inser

Re: [sqlite] Database modeling question

not an expert in this matter, but the more simple the better. I think however you should anticipate 2 situations: one person having more than one or two phone numbers, and 2 people sharing the same phone number. Next to that, maybe it would be smart to show them that you know not all phones these

[sqlite] ORDER of export on a SELECT

All, I I have a table with 2 fields that need to be exported to 2 TXT files but the order of the lines in the export should be exactly the same as in the table. I tried to do it like this: SELECT [FieldA] FROM [T1] ORDER BY rowid; and SELECT [FieldB] FROM [T1] ORDER BY rowid; but

Re: [sqlite] ORDER of export on a SELECT

Thanks for the advice, both. In the mean time we know that it indeed something else causing the problem. gert 2013/5/2 Simon Slavin > > On 2 May 2013, at 9:26am, Gert Van Assche wrote: > > > I have a table with 2 fields that need to be exported to 2 TXT files but > > t

[sqlite] finding records where a field only contains numbers

Hi all, is there a way to find all records where a field only contains non-alpha numeric characters, or numbers? thanks gert ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] finding records where a field only contains numbers

Thanks Clements, that does the trick indeed! 2013/6/12 Clemens Ladisch > Gert Van Assche wrote: > > is there a way to find all records where a field only contains non-alpha > > numeric characters, or numbers? > > This description is rather vague. > The following

[sqlite] creating a random sub group

All, my table contains a boolean SubGroup field that is set to 0 by default. I would like to update it so that for instance 100 of the 1000 records are randomly set to 1. Is this something that can be done using an SQL instruction? Thanks Gert ___ sqli

Re: [sqlite] creating a random sub group

Perfect! Wonderful. Thanks Igor. 2013/10/14 Igor Tandetnik > On 10/14/2013 11:36 AM, Gert Van Assche wrote: > >> my table contains a boolean SubGroup field that is set to 0 by default. >> I would like to update it so that for instance 100 of the 1000 records are >>

[sqlite] Update and GROUP BY

All, I have this table: DROP TABLE T; CREATE TABLE T (N, V, G); INSERT INTO T VALUES('a', 1, 'x'); INSERT INTO T VALUES('b', 3, 'x'); INSERT INTO T VALUES('c', null, 'x'); INSERT INTO T VALUES('d', 80, 'y'); INSERT INTO T VALUES('e', null, 'y'); INSERT INTO T VALUES('f', 60, 'y'); INSERT INTO T VA

Re: [sqlite] Update and GROUP BY

Igor, that is exactly what I need. I also understand how it is done now. Thanks! gert 2013/11/2 Igor Tandetnik > On 11/2/2013 1:06 PM, Gert Van Assche wrote: > >> All, I have this table: >> >> DROP TABLE T; >> CREATE TABLE T (N, V, G); >> INSERT INTO T V

Re: [sqlite] Update and GROUP BY

sqlite-users- > >boun...@sqlite.org] On Behalf Of Gert Van Assche > >Sent: Saturday, 2 November, 2013 11:07 > >To: sqlite-users > >Subject: [sqlite] Update and GROUP BY > > > >All, I have this table: > > > >DROP TABLE T; > >CREATE TABLE T (N, V

Re: [sqlite] Update and GROUP BY

. gert 2013/11/3 James K. Lowden > On Sat, 2 Nov 2013 18:06:30 +0100 > Gert Van Assche wrote: > > > CREATE TABLE T (N, V, G); > > INSERT INTO T VALUES('a', 1, 'x'); > > INSERT INTO T VALUES('b', 3, 'x'); > > INSERT INTO T V

Re: [sqlite] Update and GROUP BY

100 > Gert Van Assche wrote: > > > Thanks James -- the select query is something I could do, but the > > update one I could not get that one right. > > I was considering to create a new table based on the select query, > > but since the real data set is millions of re

Re: [sqlite] Unicode support in SQLite

Very nice! Thanks for sharing, Aleksey. 2013/11/9 Aleksey Tulinov > On 11/04/2013 11:50 AM, Aleksey Tulinov wrote: > > Hey, > > > As you can see, this is truly full Unicode collation and case mapping >> with untailored special casing. Extension provides the following functions, >> statements a

[sqlite] sqlite3 windows command line: writing the sql errors to a file (and not to the screen)

All, I have an sql script in an dbscript.sql file. To execute this script on a windows command line I use: type dbscript.sql | sqlite3 test.db3 I have put this line in a batch file: dbscript.cmd and I just execute this on the command line. (or via a Windows shortcut on my desktop)

[sqlite] sqlite3 windows command line: writing the sql errors to a file (and not to the screen)

All, I have an sql script in an dbscript.sql file. To execute this script on a windows command line I use:                type dbscript.sql | sqlite3 test.db3 I have put this line in a batch file: dbscript.cmd and I just execute this on the command line. (or via a Windows shortcut on my desktop)

Re: [sqlite] sqlite3 windows command line: writing the sql errors to a file (and not to the screen)

Thank you all for this answer & sorry for the double post. That was not intentional at all. The solution is perfect! gert ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] how to merge data from fields... if this is possible.

All, I have a dataset that looks like this: rowid ; fieldA ; fieldB 1 ; val1 ; This is a 2 ; val1 ; small 3 ; val1 ; test. 4 ; val2 ; The proof is in 5 ; val2 ; the pudding. And I would like to merge all values in fieldB when the value in fieldA is the same. rowid

[sqlite] rearranging 2 fields into 1 based on an order indicator in 2 other fields

All, I have a strange table like this rowid; Field1x; Field2y; FieldAx; FieldBy 1; A; a; 0; 1 2; B; b; 4; 2 and I would need this: rowid; Field3; FieldC 1; A; 0 2; a; 1 3; b; 2 4; B; 4 So: - the contents of Field1x

[sqlite] group_concat() on a JOIN problem

All, I'm sure this is not an SQLite bug, but I hope I can get rid of it: CREATE TABLE "TA"([Groep], F1); CREATE TABLE "TB"([Groep], F2); INSERT INTO [TA]([Groep], [F1]) VALUES('1', 'ABC'); INSERT INTO [TA]([Groep], [F1]) VALUES('2', 'DE'); INSERT INTO [TA]([Groep], [F1]) VALUES('3', 'F'); INSERT

[sqlite] Creating a view

All, I don't know how to do something very simple like this. I have two table and I would like to see the value of one table as it is expressed in the other. CREATE TABLE [REFTABLE] (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, [RefItem] CHAR); INSERT INTO [REFTABLE]([RefItem]) VALUES('One'); I

Re: [sqlite] Creating a view

Thanks, Igor & Keith. gert ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQL logic error or missing database

All, hoping you can help me. I bumped into an "SQL logic error or missing database" error and I don't have a clue why this happens. It happens on the first action I take in a series of all the same actions (updates of a table). I removed the triggers, changed the indexes, checked integrity... stil

Re: [sqlite] SQL logic error or missing database

s). I'm not an educated developer, just learned through experience. No doubt I will learn a lot from this strange error. 2012/10/24 Simon Slavin > > On 24 Oct 2012, at 2:53pm, Gert Van Assche wrote: > > > I bumped into an "SQL logic error or missing database&qu

Re: [sqlite] SQL logic error or missing database

Kees, thanks. I reopened the connection and all is OK now. thanks for your help, gert 2012/10/24 Kees Nuyt > On Wed, 24 Oct 2012 15:53:39 +0200, Gert Van Assche > wrote: > > >All, hoping you can help me. > > > >I bumped into an "SQL logic error or missing dat

[sqlite] Automatically create a TreeNode?

All, I have a table like this one: CREATE TABLE [WORLD] ([Continent] , [Region] , [Country] , [TreeNode] CHAR); insert into [WORLD] values('America', null, null, '1'); insert into [WORLD] values('America', 'North', null, '1.1'); insert into [WORLD] values('America', 'Central', null, '1.2'); inser

Re: [sqlite] Automatically create a TreeNode?

Clemens, This is wonderful... I have no clue how it works, but it works. this "w2" table you are using, what is this and where do you get it from? In any case, thanks for your help. gert 2012/10/31 Clemens Ladisch > Gert Van Assche wrote: > > I have a table like this

Re: [sqlite] Automatically create a TreeNode?

Clemens, not any teacher could have explained this any better! Thanks gert 2012/11/1 Clemens Ladisch > Gert Van Assche wrote: > > 2012/10/31 Clemens Ladisch > >> UPDATE World > >> SET TreeNode = CAST((SELECT COUNT(DISTINCT Continent) > >>

Re: [sqlite] SQLite converts all unicode characters into ANSI

All I can say is this is not normal behavior. I use SQLite always with UTF-8 texts, even on command line (windows). As a test I suggest you create the db with the SQLite Expert. gert 2012/11/17 ZikO > I have a slight problem with sqlite and its text encoding. I read from > documents that sqlite

[sqlite] PHP & SQLite examples

All, I would like to understand how a PHP web page communicates with SQLite. I know something about SQLite, but nothing or not a lot about PHP. I have questions like this: - How does one take input from an input field or a check box and use it for doing an "INSERT" for instance? - How

Re: [sqlite] A new extension for sqlite to analyze the stringmetrics

Andrea, any chance I can download the latest version of the DLL? thanks gert 2014-09-28 23:28 GMT+02:00 aperi2007 : > Hi Keith, > thx for hints. > I have apply all of them. > > The only one I like to explain: > > I know the warning for the void insted of int declaration of > stringmetricsFunc.

Re: [sqlite] A new extension for sqlite to analyze the stringmetrics

Sorry, stupid me... I clicked on the wrong link. 2014-10-27 17:32 GMT+01:00 Gert Van Assche : > Andrea, any chance I can download the latest version of the DLL? > > thanks > > gert > > 2014-09-28 23:28 GMT+02:00 aperi2007 : > >> Hi Keith, >> thx for hints. &g

[sqlite] Interrupt SQL query in SHELL

Hi all, When running SQLite3 from command line, is there a way to interrupt the CMD file when a condition is true? I'm importing a lot of plain text files that should all count the same number of lines, so I want to compare the record count of all imported data with the first file that was import

[sqlite] Interrupt SQL query in SHELL

initely not the right way to do it. If I understand the doc correctly, it should be an expression, but I don't see how I can do this... thanks gert 2015-04-01 20:44 GMT+02:00 Richard Hipp : > On 4/1/15, Gert Van Assche wrote: > > > > My question is: when the import fails (de

[sqlite] Interrupt SQL query in SHELL

Ryan, Simon, thank you both for the help. The script works fine, and I now also understand how to use this bail mechanism. It is great! once more: THANK YOU! gert 2015-04-01 23:17 GMT+02:00 Simon Slavin : > > On 1 Apr 2015, at 8:09pm, Gert Van Assche wrote: > > > But this is

Re: [sqlite] LIKE IN

I think this will work: INSERT INTO queries VALUES ('Alex'); INSERT INTO queries VALUES ('Rob'); select * from names where exists ( select query from queries where names.name like '%'||query||'%' ); On Fri, 22 Nov 2019 at 15:19, David Raymond wrote: > Or alternatively something like: >

Re: [sqlite] LIKE IN

query||'%'; SELECT name from names where exists ( select query from queries where names.name like '%'||query||'%' ); On Sat, 23 Nov 2019 at 11:34, Gert Van Assche wrote: > I think this will work: > > INSERT INTO queries VALUES ('Alex');

Re: [sqlite] LIKE IN

27;s anything at all that matches, > then go with exists. > > > > -Original Message- > From: sqlite-users On > Behalf Of Gert Van Assche > Sent: Saturday, November 23, 2019 5:43 AM > To: SQLite mailing list > Subject: Re: [sqlite] LIKE IN > >