On 09 Mar 2014 at 22:17, Igor Tandetnik <i...@tandetnik.org> wrote:
> On 3/9/2014 6:05 PM, Tim Streater wrote:
>> I have a table with one column containing file paths, such as /path/to/file
>> and /path/to/my/otherfile. Now I want to change all entries where the path
>> starts as /path/to/ to /path/from/. Getting a candidate list is easy, and I
>> can then make the changes in PHP and rewrite the rows, but I wondered if
>> there was a clever way to do it all in SQLite in, essentially, one statement.
>> A quick look persuades me there are not enough functions built into SQLite
>> for that, but confirmation would be handy.
>
> update mytable set path='/path/from/' || substr(path,
> length('/path/to/') + 1)
> where substr(path, 1, length('/path/to/')) = '/path/to/';
Dammit, I looked up and down for 'strlen' and passed over 'length'! I had been
thinking about:
update mytable set path='/path/from/' || substr(path, length('/path/to/') + 1)
where path like '/path/to/%';
that way I anchor to the start of the path.
--
Cheers -- Tim
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
