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

replace, sql, sql-update, sqlite

Solution

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

Problem

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).

Original source