malcom <[EMAIL PROTECTED]> writes:
> Hello,
> I have a sqlite column with a string. This string is composed by n
> different lines separated by an \n character. Each line is composed by
> <key>: <value>. So my final string is something like this:
>
> <key_1>: <value_1_bla_bla>\n
> <key_2>: <value_2_bla_bla>\n
> <key_3>: <value_3_bla_bla>
>
> Now I need to search *only* inside a particular key value. For example
> I would to see if the value of key_2 contains a string 'test' (and if
> possible return the entire value of key key_2).
This is a perfect use for having a different table contain your key/value
pairs. Consider this:
CREATE TABLE item
(
item_id INTEGER PRIMARY KEY
);
CREATE TABLE attributes
(
item_id INTEGER REFERENCES item,
data_key TEXT,
data_value TEXT
);
INSERT INTO item VALUES (1);
INSERT INTO attributes VALUES (1, 'description', 'My first item');
INSERT INTO attributes VALUES (1, 'quantity', '3');
INSERT INTO item VALUES (2);
INSERT INTO attributes VALUES (2, 'description', 'My second item');
INSERT INTO attributes VALUES (2, 'quantity', '7');
-- now we can easily search for a particular value within a specific key
-- or, as you requested, the whole value if some text exists within the
-- value
SELECT item_id, data_value
FROM attributes
WHERE data_key = 'description'
AND data_value GLOB '*second*';
This didn't answer your question, but may provide a better method to
accomplish your goals.
Derrell
