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)
--
================================================================================================
