Re: [sqlite] Making string changes in a table

[Tim Streater]

On 09 Mar 2014 at 22:48, Igor Tandetnik  wrote: 

> On 3/9/2014 6:37 PM, Tim Streater wrote:
>> 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.
>
> Be careful about '/path/to/' itself containing percent signs or underscores.

Thanks for the reminder, Igor.



--
Cheers  --  Tim
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Making string changes in a table

[Igor Tandetnik]

On 3/9/2014 6:37 PM, Tim Streater wrote:

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.


Be careful about '/path/to/' itself containing percent signs or underscores.
--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Making string changes in a table

[Tim Streater]

On 09 Mar 2014 at 22:17, Igor Tandetnik  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

Re: [sqlite] Making string changes in a table

[Igor Tandetnik]

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/';

--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Making string changes in a table

[Zsbán Ambrus]

On 3/9/14, Simon Slavin  wrote:
> Check out REPLACE():
>
> Technically speaking this might mess up if the string '/path/to/' occurs in
> the middle of the string as well as at its beginning,

For that reason, I think it would be better to use the substr function.

Ambrus
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Making string changes in a table

[Simon Slavin]

On 9 Mar 2014, at 10:05pm, 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.

Check out REPLACE():



Technically speaking this might mess up if the string '/path/to/' occurs in the 
middle of the string as well as at its beginning, so you might do some paranoid 
testing if you think this may occur.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users