How to update an SQLite database with a search and replace query?

Question

My SQL knowledge is very limited, specially about SQLite, although I believe this is will be some sort of generic query... Or maybe not because of the search and replace...

I have this music database in SQLite that has various fields of course but the important ones here the "media_item_id" and "content_url".

Here's an example of a "content_url":

file:///c:/users/nazgulled/music/band%20albums/devildriver/%5b2003%5d%20devildriver/08%20-%20what%20does%20it%20take%20(to%20be%20a%20man).mp3

I'm looking for a query that will search for entries like those, where "content_url" follows that pattern and replace it (the "content_url") with something else.

For instance, a generic "content_url" can be this:

file:///c:/users/nazgulled/music/band%20albums/BAND_NAME/ALBUM_NAME/SONG_NAME.mp3

And I want to replace all these entries with:

file:///c:/users/nazgulled/music/bands/studio%20albums/BAND_NAME/ALBUM_NAME/SONG_NAME.mp3

How can I do it in one query?

P.S: I'm using Firefox SQLite Manager (couldn't find a better and free alternative for Windows).

Answer 1

You are probably looking for the replace function.

For example,

update table_name set 
  content_url = replace(content_url, 'band%20albums', 'bands/studio%20albums')
where
  content_url like '%nazgulled/music/band_20albums/%';

More documentation at http://sqlite.org/lang_corefunc.html