SQL 如何使用搜索和替换查询更新 SQLite 数据库？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1061692/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to update an SQLite database with a search and replace query?
提问by rfgamaral
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...
我的 SQL 知识非常有限，特别是关于 SQLite，虽然我相信这将是某种通用查询......或者可能不是因为搜索和替换......
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".
我在 SQLite 中有这个音乐数据库，它当然有各种领域，但这里最重要的是“media_item_id”和“content_url”。
Here's an example of a "content_url":
这是“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.
我正在寻找一个查询来搜索类似的条目，其中“content_url”遵循该模式并将其（“content_url”）替换为其他内容。
For instance, a generic "content_url" can be this:
例如，一个通用的“content_url”可以是这样的：
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).
PS：我正在使用 Firefox SQLite Manager（找不到更好的免费 Windows 替代品）。
回答by laalto
You are probably looking for the replacefunction.
您可能正在寻找该replace功能。
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
