How to update this in SQLite?

Question

I have a value in a table row as given below:

/mnt/sdcard/Tutorialtwo/chapter_1b.png

I want to update the table so that it changes the above to this:

/storage/sdcard1/Tutorialtwo/chapter_1b.png

How do I do this in SQLite?

Answer 1

You could use substr

substr(X,Y,Z), substr(X,Y)
The substr(X,Y,Z) function returns a substring of input string X that begins with the Y-th character and which is Z characters long. If Z is omitted then substr(X,Y) returns all characters through the end of the string X beginning with the Y-th.

to extract the '/Tutorialtwo/chapter_1b.png' suffix and then a string concatenation to put the new '/storage/sdcard1' prefix on. The SQL version would look something like this:

update t
set c = '/storage/sdcard1' || substr(c, 12)
where ...

where t is the table name and c is the column in question.