[sqlite] Parsing the contents of a field
On 2016/01/16 4:53 PM, Simon Slavin wrote: > On 16 Jan 2016, at 2:31pm, R Smith wrote: > >> There is of course no SQL function to do this, but thanks to CTE we can >> achieve it easily (though not extremely efficiently). > I thought that WITH could be used only for SELECT statements. > > Oh wait, you want to use the WITH statement as a sub-select on an INSERT > statement. Does that work ? The example I posted simply SELECTs the values, but it can as easily be piped to a table via an INSERT statement or such. The OP said indeed he wants to push the values to a table and from there read it. This can even be an in-memory table or simply another CTE table. As to your question - WITH can be used in any place where a table-select can, with some restrictions (though my example requires nothing fancy in this regard). I quote from the documentation at: https://www.sqlite.org/lang_with.html "All common table expressions (ordinary and recursive) are created by prepending a WITH clause in front of a SELECT, INSERT, DELETE, or UPDATE statement. A single WITH clause can specify one or more common table expressions" That is to say, it is perfectly legal to do something like this: WITH C(Idx) AS ( SELECT 1 UNION ALL SELECT Idx+1 FROM C OFFSET 5 LIMIT 3 ) DELETE FROM Contacts WHERE EXISTS (SELECT 1 FROM C WHERE C.Idx=Contacts.ID); Which will delete contacts from the "Contacts" table with ID's 6, 7 and 8. Or perhaps: WITH NewPeeps(title, name, surname, tel, email) AS ( SELECT 'James', 'Jones', '555-123-7689' UNION ALL SELECT 'Jason', 'Johnson', '555-124-7689' UNION ALL SELECT 'Jeffrey', 'Jenner', '555-125-7689' ) INSERT INTO Contacts (Name, Surname, Title, Tel, Mail) SELECT name, surname, 'Mr.', tel, name||'@jworld.org' FROM NewPeeps; Which will insert the contacts into the Contacts table. Etc. Even in explicit sub-selects it works perfectly well, for example this query: SELECT * FROM ( WITH C(Idx) AS (SELECT (10+1) UNION ALL SELECT Idx+2 FROM C WHERE Idx<(20-1)) SELECT * FROM C ) Which will produce all odd numbers between 10 and 20. And then this Query will list all contacts with ID's that are the odd numbers between 10 and 20: SELECT * FROM Contacts, ( WITH C(Idx) AS (SELECT (10+1) UNION ALL SELECT Idx+2 FROM C WHERE Idx<(20-1)) SELECT * FROM C ) AS S WHERE IndexC=S.Idx Or in a correlated sub-query: SELECT ( WITH C(Idx, SqrIdx) AS ( SELECT 1,1 UNION ALL SELECT Idx+1, (Idx*Idx) FROM C WHERE Idx<1000 ) SELECT SqrIdx FROM C WHERE C.Idx=Contacts.ID LIMIT 1 ) AS SQR, Contacts.* FROM Contacts which shows the square of contact ID's next to the rest of their data. These examples are of course extremely simple, but they show the principles.
[sqlite] Parsing the contents of a field
On 17 Jan 2016, at 12:27am, R Smith wrote: > I quote from the documentation at: https://www.sqlite.org/lang_with.html > > "All common table expressions (ordinary and recursive) are created by > prepending a WITH clause in front of a SELECT, INSERT, DELETE, or UPDATE > statement. A single WITH clause can specify one or more common table > expressions" Ah, I missed that. I thought it was just SELECT. Thanks. Simon.
[sqlite] Parsing the contents of a field
On 2016/01/16 7:02 AM, audio muze wrote: >> What do you mean by "parse" ? Just to separate a string into its delimited >> substrings ? Since SQLite has no array or list variable-type there's no way >> to do that because there's no way to return the result. Can you not just >> return the value retrieved from the table and parse it in your software ? >> >> You can add your own functions to SQLite, so you could write your own parse >> function and do something like >> >> SELECT parse(myColumn) FROM myTable >> >> However this is quite complicated and not recommended for the beginner. > The fields in question have content as follows: > string1\\string2\\string3\\...\\stringx > > I want to write every record's entries to a table with each element > being a separate record i.e. > > string1 > string2 > string3 > . > . > . > stringx There is of course no SQL function to do this, but thanks to CTE we can achieve it easily (though not extremely efficiently). I have posted some time ago here some CTE query to break CSV values in a field up into their own items, I will post it now again from one of SQLitespeed tutorial scripts, it's an easy adaption to use those slashes (or whatever other separator character(s) you use). I will leave the adaption to you since I am unsure if your post is just an example or whether the actual separators are in fact slashes - but if you have difficulty adapting it, please post again with more detail and we will try to assist better. -- Example Script for un-packing CSV values (or any delimited data). -- [ Needs CTE: SQLite 3.4+ ] -- -- Remove any auto-created Tables -- DROP TABLE IF EXISTS tmpcsv; -- -- Create & Populate the Table used in the examples -- CREATE TABLE tmpcsv ( ID INTEGER PRIMARY KEY, colA TEXT, colCSV TEXT ); -- -- Insert example CSV data -- INSERT INTO tmpcsv (ID, colA, colCSV) VALUES (1, 'foo', '4,66,51,3009,2,678') , (2, 'bar', 'Sputnik,Discovery') , (3, 'baz', '101,I-95,104') , (4,'foz','Amsterdam, Beijing, London, Moscow, New York, Paris, Tokyo') ; -- This CTE Query works only on the Table above, but will run in any -- SQLite script engine. -- -- You will need to modify the table (t) and Key (t.ColA) and column -- containing the separated data (t.colCSV in this case). -- -- You may modify the separation character (comma in this case) by -- replacing all ',' with either another quoted character (such -- as '#', ';' or '|') or using the CHAR(UnicodeVal) function (for -- example CHAR(09) for TAB or CHAR(32) for SPACE, etc.) -- WITH csvrec(i, l, c, r) AS ( SELECT t.colA, 1, t.colCSV||',', '' FROM tmpcsv AS t WHERE 1 UNION ALL SELECT i, instr( c, ',' ) AS vLength, substr( c, instr( c, ',' ) + 1) AS vRemainder, trim( substr( c, 1, instr( c, ',' ) - 1) ) AS vCSV FROM csvrec WHERE vLength > 0 ) SELECT t.ID, t.colA, cr.r AS colCSV FROM tmpcsv AS t, csvrec AS cr WHERE t.colA = cr.i AND cr.r <> '' ORDER BY t.ID, t.colA ; -- ID |colA | colCSV -- === | = | === -- 1|foo| 2 -- 1|foo| 3009 -- 1|foo| 4 -- 1|foo| 51 -- 1|foo| 66 -- 1|foo| 678 -- 2|bar| Discovery -- 2|bar| Sputnik -- 3|baz| 101 -- 3|baz| 104 -- 3|baz| I-95 -- 4|foz| Amsterdam -- 4|foz| Beijing -- 4|foz| London -- 4|foz| Moscow -- 4|foz| New York -- 4|foz| Paris -- 4|foz| Tokyo -- -- Example Cleanup -- DROP TABLE IF EXISTS tmpcsv; -- Script Stats: Total Script Execution Time: 0d 00h 00m and 00.037s -- Total Script Query Time: 0d 00h 00m and 00.012s -- Total Database Rows Changed: 4 -- Total Virtual-Machine Steps: 1196 -- Last executed Item Index:5 -- Last Script Error: -- -- 2015-04-09 17:14:51.419 | [Success]Script Success. -- 2015-04-09 17:14:51.421 | [Success]Transaction Rolled back. -- --- DB-Engine Logs (Contains logged information from all DB connections during run) -- -- [2015-04-09 17:14:51.369] APPLICATION : Script D:\Documents\SQLiteScripts\csv_unpack.sql started at 17:14:51.369 on 09 April. -- [2015-04-09 17:14:51.415] ERROR (284) : automatic index on csvrec(i) --
[sqlite] Parsing the contents of a field
On 16 Jan 2016, at 2:31pm, R Smith wrote: > There is of course no SQL function to do this, but thanks to CTE we can > achieve it easily (though not extremely efficiently). I thought that WITH could be used only for SELECT statements. Oh wait, you want to use the WITH statement as a sub-select on an INSERT statement. Does that work ? Simon.
[sqlite] Parsing the contents of a field
On 16 Jan 2016, at 5:02am, audio muze wrote: > The fields in question have content as follows: > string1\\string2\\string3\\...\\stringx > > I want to write every record's entries to a table with each element > being a separate record i.e. This would require a different number of INSERT commands depending on how many strings there are in the field. It may be possible to do this in SQL but it would be complicated. Do it in your programming language, not in SQL commands. Simon.
[sqlite] Parsing the contents of a field
> What do you mean by "parse" ? Just to separate a string into its delimited > substrings ? Since SQLite has no array or list variable-type there's no way > to do that because there's no way to return the result. Can you not just > return the value retrieved from the table and parse it in your software ? > > You can add your own functions to SQLite, so you could write your own parse > function and do something like > > SELECT parse(myColumn) FROM myTable > > However this is quite complicated and not recommended for the beginner. The fields in question have content as follows: string1\\string2\\string3\\...\\stringx I want to write every record's entries to a table with each element being a separate record i.e. string1 string2 string3 . . . stringx When done writing that table I'll use select distinct to obtain a unique list of that attribute and it becomes my reference/ lookup table.
[sqlite] Parsing the contents of a field
If it's OK to use the sqlite3 cmd line shell then try this:
-- create test input table X
create table X (what text);
insert into X values ('abc,def');
-- write X to a file
.output mydata.csv
select * from X;
.output stdout
-- read it back in to parse it
create table Y (a text, b text);
.mode csv
.import mydata.csv Y
-- check Y - add a limit clause if Y is large
select * from Y;
On Wed, Jan 13, 2016 at 12:42 AM, audio muze wrote:
> I have a table of roughly 500k records with a number of fields
> containing delimited text that needs to be parsed and written to
> separate tables as a master lists. In order to do this I need to
> parse the field contents, however, I don't see any functions within
> SQLite to enable that. The number of delimited entries embedded in a
> field can vary from none to as man as 20/30. Is there an addin I can
> compile with SQLite that provides the ability to parse a string?
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
--
Statistics & Software Consulting
GKX Group, GKX Associates Inc.
tel: 1-877-GKX-GROUP
email: ggrothendieck at gmail.com
[sqlite] Parsing the contents of a field
Hi, On Wed, Jan 13, 2016 at 10:05 AM, Jim Morris wrote: > Might be doable with a recursive CTE > > On 1/13/2016 1:22 AM, Bart Smissaert wrote: >> >> It probably can be done with just SQLite's built-in text functions such as >> instr and substr, >> although with 20 to 30 items it may get a bit messy and complex. >> >> RBS >> >> On Wed, Jan 13, 2016 at 5:42 AM, audio muze wrote: >> >>> I have a table of roughly 500k records with a number of fields >>> containing delimited text that needs to be parsed and written to >>> separate tables as a master lists. In order to do this I need to >>> parse the field contents, however, I don't see any functions within >>> SQLite to enable that. The number of delimited entries embedded in a >>> field can vary from none to as man as 20/30. Is there an addin I can >>> compile with SQLite that provides the ability to parse a string? You are way better off with a Perl script. Thank you. >>> ___ >>> sqlite-users mailing list >>> sqlite-users at mailinglists.sqlite.org >>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >>> >> ___ >> sqlite-users mailing list >> sqlite-users at mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Parsing the contents of a field
You will thank yourself by using a scripting language such Ruby, php, or python. Is there a reg ex library for sqlite that could be employed? On Tue, Jan 12, 2016 at 11:42 PM, audio muze wrote: > I have a table of roughly 500k records with a number of fields > containing delimited text that needs to be parsed and written to > separate tables as a master lists. In order to do this I need to > parse the field contents, however, I don't see any functions within > SQLite to enable that. The number of delimited entries embedded in a > field can vary from none to as man as 20/30. Is there an addin I can > compile with SQLite that provides the ability to parse a string? > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] Parsing the contents of a field
It probably can be done with just SQLite's built-in text functions such as instr and substr, although with 20 to 30 items it may get a bit messy and complex. RBS On Wed, Jan 13, 2016 at 5:42 AM, audio muze wrote: > I have a table of roughly 500k records with a number of fields > containing delimited text that needs to be parsed and written to > separate tables as a master lists. In order to do this I need to > parse the field contents, however, I don't see any functions within > SQLite to enable that. The number of delimited entries embedded in a > field can vary from none to as man as 20/30. Is there an addin I can > compile with SQLite that provides the ability to parse a string? > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] Parsing the contents of a field
On 13 Jan 2016, at 5:42am, audio muze wrote: > The number of delimited entries embedded in a > field can vary from none to as man as 20/30. Is there an addin I can > compile with SQLite that provides the ability to parse a string? What do you mean by "parse" ? Just to separate a string into its delimited substrings ? Since SQLite has no array or list variable-type there's no way to do that because there's no way to return the result. Can you not just return the value retrieved from the table and parse it in your software ? You can add your own functions to SQLite, so you could write your own parse function and do something like SELECT parse(myColumn) FROM myTable However this is quite complicated and not recommended for the beginner. Simon.
[sqlite] Parsing the contents of a field
I have a table of roughly 500k records with a number of fields containing delimited text that needs to be parsed and written to separate tables as a master lists. In order to do this I need to parse the field contents, however, I don't see any functions within SQLite to enable that. The number of delimited entries embedded in a field can vary from none to as man as 20/30. Is there an addin I can compile with SQLite that provides the ability to parse a string?
[sqlite] Parsing the contents of a field
Might be doable with a recursive CTE On 1/13/2016 1:22 AM, Bart Smissaert wrote: > It probably can be done with just SQLite's built-in text functions such as > instr and substr, > although with 20 to 30 items it may get a bit messy and complex. > > RBS > > On Wed, Jan 13, 2016 at 5:42 AM, audio muze wrote: > >> I have a table of roughly 500k records with a number of fields >> containing delimited text that needs to be parsed and written to >> separate tables as a master lists. In order to do this I need to >> parse the field contents, however, I don't see any functions within >> SQLite to enable that. The number of delimited entries embedded in a >> field can vary from none to as man as 20/30. Is there an addin I can >> compile with SQLite that provides the ability to parse a string? >> ___ >> sqlite-users mailing list >> sqlite-users at mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Parsing the contents of a field
Hi: Not sure what is the cost to first export the fields a flat file? Try https://github.com/elau1004/TFR4SQLite/wiki (may or may not fit your needs). It was intended to query flatfile with crazy delimiters, using SQLite query engine. If you can SELECT from a text file then you can insert the result into another table. Regards. -Original Message- From: audio muze <audiom...@gmail.com> To: sqlite-users Sent: Tue, Jan 12, 2016 9:42 pm Subject: [sqlite] Parsing the contents of a field I have a table of roughly 500k records with a number of fields containing delimited text that needs to be parsed and written to separate tables as a master lists. In order to do this I need to parse the field contents, however, I don't see any functions within SQLite to enable that. The number of delimited entries embedded in a field can vary from none to as man as 20/30. Is there an addin I can compile with SQLite that provides the ability to parse a string? ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
