CODEWITHSUNDEEP
sqlsqlite
colin
colin

Reputation: 3103

SQLite - replace part of a string

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

?

Upvotes: 127

Views: 104146

Answers (3)

bugmenot123
bugmenot123

Reputation: 1136

And if you just want to do it in a query without lasting consequences:

SELECT fieldA, replace(field, 'C:\afolder\', 'C:\anewfolder\'), fieldB FROM table;

Upvotes: 11

vladkras
vladkras

Reputation: 17228

@Andrew answer is partially correct. No need to use WHERE clause here:

  1. Only fields containing C:\afolder will be affected anyway, no reason to check it. It's excessive.
  2. 'C:\afolder\%' will choose only fields starting with C:\afolder\ only. What if you have this path inside string?

So the correct query is just:

UPDATE table SET field = replace( field, 'C:\afolder\', 'C:\anewfolder\');

Upvotes: 35

Andrew
Andrew

Reputation: 2620

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\%';

Upvotes: 243

Related Questions