121

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

?

mu is too short
  • 426,620
  • 70
  • 833
  • 800
colin
  • 2,983
  • 6
  • 41
  • 49

3 Answers3

235

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

Andrew
  • 2,540
  • 1
  • 16
  • 9
  • 9
    While the WHERE clause is not necessary, it does give a little bit of peace of mind. Without WHERE, SqlLite will tell you that every row in your table was affected. By using the WHERE, you'll only get the dozen or so rows you expected instead of potentially thousands. – Weston Wedding May 08 '17 at 18:37
  • 5
    @WestonWedding I have compared query time with and without WHERE clause. Without where query is taking twice time. – Parag Bafna Feb 23 '18 at 17:58
  • FYI, this and vladkras's solution is case sensitive. I experimented with inserting LIKE statements to make it case insensitive, but I couldn't get it to work, I don't think it's possible with the SQLite Replace command. – ShadowLiberal Nov 05 '18 at 21:24
  • Thanks. Darktable uses a sqlite db to store the location of images, so this saved me about 9000 changes! – Phil Jan 19 '19 at 14:40
32

@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\');
Community
  • 1
  • 1
vladkras
  • 16,483
  • 4
  • 45
  • 55
  • Will this replace the string in every string in the column "field"? – fifaltra Mar 02 '16 at 12:45
  • @fifaltra yes it will – resedasue Apr 16 '16 at 19:09
  • 1
    Note that I would not recommend to use replace() when you want to update file system paths in case they are relative (instead of absolute). See https://stackoverflow.com/questions/50161090/replace-path-string-in-sqlite-db-causes-unexpected-violated-unique-constraint – MShekow May 04 '18 at 04:29
10

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;
bugmenot123
  • 1,069
  • 1
  • 18
  • 33