lundi 26 octobre 2015

sqlite string replace/delete

I have a column in my database table with name tags which contains comma separated strings and it has records like this-

index | tags
-------------
    1 | a,b,c
    2 | b
    3 | c
    4 | z
    5 | b,a,c
    6 | p,f,w
    7 | a,c,b

(for simplicity i am denoting strings with characters)

Now i want to replace/delete particular string.

Delete - say I want to delete b from all rows. If tags column become empty after this operation that row/record should be deleted (index 2 in this case). My records should look like this after this operation.

index | tags
-------------
    1 | a,c
    3 | c
    4 | z
    5 | a,c
    6 | p,f,w
    7 | a,c

Replace - say I want to replace all a with k on original records

index | tags
-------------
    1 | k,b,c
    2 | b
    3 | c
    4 | z
    5 | b,k,c
    6 | p,f,w
    7 | k,c,b

Question - I am thinking of using replace function somehow but not sure how to meet above requirement with that. Can i do this in a single sql command? If not please suggest best way to do this (may be multiple sql commands).

Aucun commentaire:

Enregistrer un commentaire