CREATE TABLE tmpcsv (
ID INTEGER PRIMARY KEY,
colA TEXT,
colCSV TEXT
);
INSERT INTO tmpcsv (colA, colCSV) VALUES
('foo', '4,66,51,3009,2,678'),
('bar', 'Sputnik,Discovery'),
('baz', '101,I-95,104');
WITH csvrec(i,l,c,r) AS (
SELECT tmpcsv.ID, 1, colCSV||',', '' FROM tmpcsv
UNION ALL
SELECT i,
instr(c,',') AS vLen,
substr(c,instr(c,',')+1) AS vRem,
substr(c,1,instr(c,',')-1) AS vCSV
FROM csvrec
WHERE vLen>0
)
SELECT t.ID, t.colA, rt.r FROM tmpcsv AS t, csvrec AS rt
WHERE t.ID=rt.i AND rt.r<>''
ORDER BY t.ID
LIMIT 100
Results:
ID colA r
--- ---- ---
1 foo 4
1 foo 66
1 foo 51
1 foo 3009
1 foo 2
1 foo 678
2 bar Sputnik
2 bar Discovery
3 baz 101
3 baz I-95
3 baz 104
:)
(PS: The Limit is not strictly necessary, but advised during testing to avoid
accidental eternal loops).
On 2014/04/04 23:12, peter korinis wrote:
Thanks, Simon.
The database was created by a developer as part of a larger project
involving a mobile app and portal. the portal is written in which calls the
db. so that's where the 'parsing' is done. But unfortunately a SQL alone
cannot provide the data in this case. This portal is in production so we're
not about to rewrite the code with a db redesign.
Does SQL have any string commands, like REGEXP or something else to strip
out the commas, and transform the string into multiple discrete values, then
use those perhaps in a SQL subquery . or something like that?
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
