My table :
| id | condition_value | parameter |
|---|---|---|
| 1 | List(1,30) | 40 |
| 2 | 44 | 50 |
I need my query to extract parameter based on condition_value:
SELECT parameter WHERE condition_value=25
My query does not return anything. What I need to be returned is 40 (because 25 is in [1,2,3...,30].
You could use something like (see better below):-
SELECT
parameter
FROM simple
WHERE 25 BETWEEN
CASE
WHEN instr(condition_value,'List(')
THEN CAST(substr(condition_value,length('List(')+1,(instr(condition_value,',')-length('List('))-1) AS INTEGER)
ELSE CAST(condition_value AS INTEGER)
END
AND
CASE
WHEN instr(condition_value,'List(')
THEN CAST(substr(condition_value,instr(condition_value,',')+ 1,instr(condition_value,')')-instr(condition_value,',')-1) AS INTEGER)
ELSE CAST(condition_value AS INTEGER)
END
;
However, a better, more flexible and simpler, solution would be to introduce another column so that a range of values can be specified.
e.g.
CREATE TABLE IF NOT EXISTS better (id INTEGER PRIMARY KEY, cvlow, cvhigh, parameter);
The following demonstrates both:-
DROP TABLE IF EXISTS simple;
CREATE TABLE IF NOT EXISTS simple (id INTEGER PRIMARY KEY, condition_value TEXT, parameter INTEGER);
INSERT INTO simple (condition_value,parameter) VALUES
('List(1,30)',40),
('List(25,25)',41),
('List(-100,25)',42),
('List(-1,-25)',43),
('List(1,2500000)',44),
(44,50)
;
SELECT
parameter
FROM simple
WHERE 25 BETWEEN
CASE
WHEN instr(condition_value,'List(')
THEN CAST(substr(condition_value,length('List(')+1,(instr(condition_value,',')-length('List('))-1) AS INTEGER)
ELSE CAST(condition_value AS INTEGER)
END
AND
CASE
WHEN instr(condition_value,'List(')
THEN CAST(substr(condition_value,instr(condition_value,',')+ 1,instr(condition_value,')')-instr(condition_value,',')-1) AS INTEGER)
ELSE CAST(condition_value AS INTEGER)
END
;
DROP TABLE IF EXISTS simple;
/* BETTER simpler and with greater flexibility*/
CREATE TABLE IF NOT EXISTS better (id INTEGER PRIMARY KEY, cvlow, cvhigh, parameter);
INSERT INTO better (cvlow,cvhigh,parameter) VALUES
(1,30,40),
(25,25,41),
(-100,25,42),
(-1,-25,43),
(1,2500000,44),
(44,44,50) /* the equivalent of the single value */
;
SELECT parameter FROM better WHERE 25 BETWEEN cvlow AND cvhigh;
DROP TABLE IF EXISTS better;
The results being the same:-
and
