Author: Paulo van Breugel
Updated on: 2014-03-09
Replace or delete strings in a field in a SQLite database.
Use update and replace, see example below, where the string <iframe> is replaced by <a>iframe in the column COL of the table MyTable (source: Stackexchange)
UPDATE MyTable SET mycolumn = replace(mycolumn,'<iframe','<a>iframe') UPDATE MyTable SET mycolumn = replace(mycolumn,'> </iframe>','</a>')
You can use replace also to delete parts of a string. For example, if we want to remove <iframe>:
UPDATE MyTable SET mycolumn = REPLACE(mycolumn, '<iframe>', '');
If you only want to remove the string when it appears at the start of the field, you can use:
UPDATE MyTable SET mycolumn = REPLACE(mycolumn, '<iframe>', '') WHERE mycolumn LIKE '<iframe> %';
Alternatively, you can use the SUBSTR command (see this tutorial for datails).
UPDATE MyTable SET mycolumn = SUBSTR(mycolumn, 9) WHERE mycolumn LIKE '<iframe> %';
Special case - replace NULL
If you want to replace NULL values in a table:
UPDATE MyTable SET mycolumn=-999 WHERE mycolumns ISNULL
If you have questions
If you have questions or comments about the text, let me know. You can use this contact form. Please make sure to include the page title ("Replace string in SQLite table") or page name ("replace strings").