[Solved] How to Update this data using SQL? [duplicate]

Something like this will work:

UPDATE yourtable
SET yourfield = MID(yourfield,INSTR(yourfield,"/Documents/"));

INSTR locates the position of the string /Documents/, and MID gets everything beginning from there.


  1. This maybe won’t work as you expect it when you have something like
    /Documents/Documents/ in your path string.
  2. Depending on your RDBMS
    MID and INSTR may not be available, but most RDBMS support them.


After my reply you’ve updated that SQLite is the RDBMS of your choice – this makes things more difficult. There ain’t no INSTR in SQLite, so most people will advise you to parse the result with a program, change it there and update the data then – for example in this SO post.

However… since your two examples both have a fixed directory name length (in total 18 characters), there is the tiny chance that in your case you could do it slightly easier:

UPDATE yourtable
SET yourfield = SUBSTR(yourfield,18);

I haven’t tried this yet, but maybe this works for you.


solved How to Update this data using SQL? [duplicate]