android - Update text column based on content with a single lookup in SQLite -

in ios , android projects, have sqlite table looks this:

id    words ----------- 1     apple, banana, orange, peach, strawberry 2     car, plane, orange 3     sheep, car, plane, horse, cow .     ... . . 

the words column text column holds comma deliminated list of words.

i want update word list of particular row adding word front of list. list should have no more 5 items delete last word if necessary.

for example, if updating row id 1 cherry get

cherry, apple, banana, orange, peach 

or if doing same update on row id 2

cherry, car, plane, orange 

my question

i know query row, process text, , update row. however, require 2 table lookups, 1 query , 1 update. possible single update lookup?

i know there replace() function not replacing here. i'm not incrementing integer. didn't see obvious in sqlite core functions.

correct solution

the words column text column holds comma deliminated list of words.

1 nf. column contains atomic data. normalize schema clean code , better performance.

workaround solution:

sqlite not have built-in reverse function, why bit ugly:

create table mytable(id    integer  not null, words text );  insert mytable(id,words) values (1,'apple, banana, orange, peach, strawberry'); insert mytable(id,words) values (2,'car, plane, orange'); insert mytable(id,words) values (3,'sheep, car, plane, horse, cow'); insert mytable(id,words) values (4,'sheep, cherry, plane, horse, cow');  update mytable set words = case              when (length(words) - length(replace(words, ',', ''))) < 4              'cherry, ' || words             else  substr('cherry, ' || words, 1,                         length(words) + length('cherry, ') -                         length(substr(substr(                       substr( substr(words, instr(words,',')+1), instr(substr(words, instr(words,',')+1), ',')+1),                       instr(substr( substr(words, instr(words,',')+1), instr(substr(words, instr(words,',')+1), ',')+1), ',') + 1),                       instr(substr(                       substr( substr(words, instr(words,',')+1), instr(substr(words, instr(words,',')+1), ',')+1),                       instr(substr( substr(words, instr(words,',')+1), instr(substr(words, instr(words,',')+1), ',')+1), ',') + 1),',')+1)) -1                       )             end words not '%cherry%';  select * mytable; 

sqlfiddledemo

to make more general need change cherry value.

output:

╔════╦══════════════════════════════════════╗ ║ id ║                words                 ║ ╠════╬══════════════════════════════════════╣ ║  1 ║ cherry, apple, banana, orange, peach ║ ║  2 ║ cherry, car, plane, orange           ║ ║  3 ║ cherry, sheep, car, plane, horse     ║ ║  4 ║ sheep, cherry, plane, horse, cow     ║ ╚════╩══════════════════════════════════════╝ 

how works:

• update ... words not '%cherry%'; not update rows have cherry already
• set words = case when (length(words) - length(replace(words, ',', ''))) < 4 if number of delimeters (commas) lower 4 concatenate value string
• if number of commas 4 means has 5 values. add desired string @ beginning, use substring 1 last comma

---

sql server version comparison:

declare @val varchar(100) = 'cherry';  update mytable set words = case              when len(words)-len(replace(words, ',', '')) < 4 @val + ', ' + words             else left(@val + ', ' + words,                       len(@val + ', ' + words) - charindex(',', reverse(words)))             end words not '%'+ @val  +'%';  select * mytable; 

livedemo