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

sql sqlite sql-update replace
rfgamaral picture rfgamaral · Jun 30, 2009 · Viewed 45.5k times · Source

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

laalto picture laalto · Jun 30, 2009

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

Related questions

Sql query for updating database if value is not null?

I am having a table which has about 17 fields. I need to perform frequent updates in this table. But the issue is each time I may be updating only a few fields. Whats the best way to write a query …

Read More
Select first row in each GROUP BY group?

As the title suggests, I'd like to select the first row of each set of rows grouped with a GROUP BY. Specifically, if I've got a purchases table that looks like this: SELECT * FROM purchases; My Output: id | customer | total …

Read More
How to list the tables in a SQLite database file that was opened with ATTACH?

What SQL can be used to list the tables, and the rows within those tables in an SQLite database file - once I have attached it with the ATTACH command on the SQLite 3 command line tool?

Read More