Menu
Is it possible using SQL in an SQLite table to replace part of a string?
For example, I have a table where one of the fields holds the path to a file. Is it possible to replace parts of the string so that, e.g.
c:\afolder\afilename.bmp becomes
c:\anewfolder\afilename.bmp ?
256
SQLite substr() returns the specified number of characters from a particular position of a given string. A string from which a substring is to be returned. An integer indicating a string position within the string X. An integer indicating a number of characters to be returned.
First, specify the table where you want to update after the UPDATE clause. Second, set new value for each column of the table in the SET clause. Third, specify rows to update using a condition in the WHERE clause. The WHERE clause is optional.
Double-quotes in SQLite identifiers are escaped as two double quotes. SQLite identifiers preserve case, but they are case-insensitive towards ASCII letters. It is possible to enable unicode-aware case-insensitivity.
You can use the built in replace() function to perform a string replace in a query.
Other string manipulation functions (and more) are detailed in the SQLite core functions list
The following should point you in the right direction.
UPDATE table SET field = replace( field, 'C:\afolder\', 'C:\anewfolder\' ) WHERE field LIKE 'C:\afolder\%';
134
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
