Header

Replace string in SQLite table

Author: Paulo van Breugel
Updated on: 2014-03-09

Topic

Replace or delete strings in a field in a SQLite database.

Answer

Replace text

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

Delete text

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