On 2018/11/30 2:52 PM, Simon Walter wrote:
I am trying to make a prepared statement that has a parameter such as
"1, 893, 121212". obviously it is text (a string) but it cannot be
quoted or the result will be:
SELECT id, data FROM val WHERE id IN ("1, 893, 121212");
I understand normally I would need the quotes, such as:
SELECT id, name, val FROM obj WHERE name = ?
There is one slightly convoluted but easy copy-paste solution - if this
is not part of a very cpu-intensive query and you just want an easy way
to get this done this one time...
The following Query will do exactly what you need:
(It's simply a CTE that unravels the comma-separated format line you
give in ? into a table form and then uses that to do the lookup with)
WITH csvrec(i, l, c, r) AS (
SELECT 1, 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 id, data
FROM val
WHERE id IN (SELECT r FROM csvrec WHERE r <> '');
;
-- The "... WHERE r <> '' " bit might not be needed here depending on your use
case.
This one you can run straight in sqlite to more clearly see what is happening
inside the CTE bit using your '1, 893, 121212' example (for fun):
WITH csvrec(i, l, c, r) AS (
SELECT 1, 1,'1, 893, 121212'||',', ''
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 * FROM csvrec
;
Disclaimer: This comes as part of the sqlitespeed install example
scripts and as such are simplified to handle basic Comma-separated text
only and does not cover all the very complex multi-quoted, multi-line
stuff that might be found in a complex CSV data file as described in
RFC4180 - so as long as you control the format of the "csv" input text,
all is well.
Cheers,
Ryan
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
