Mass text substitution on fields

Tell us about your wildest feature dreams. Or just harmless suggestions for improvement.
Post Reply
randian
Addicted to Bruji
Addicted to Bruji
Posts: 50
Joined: Tue Jan 14, 2014 8:07 pm

Mass text substitution on fields

Post by randian »

For example, edit the url field of all selected items and replace
http://www.amazon.com
with
https://www.amazon.com

Preferably, with optional regex support for more sophisticated substitutions.
User avatar
Conor
Top Dog
Posts: 5343
Joined: Sat Jul 03, 2004 12:58 pm
Contact:

Re: Mass text substitution on fields

Post by Conor »

Thanks for the feedback. As you probably know you can use the backend SQL for now, but pointing it out for others finding this thread.
randian
Addicted to Bruji
Addicted to Bruji
Posts: 50
Joined: Tue Jan 14, 2014 8:07 pm

Re: Mass text substitution on fields

Post by randian »

Conor wrote:Thanks for the feedback. As you probably know you can use the backend SQL for now, but pointing it out for others finding this thread.
Thanks. I tried that and it worked, but PocketPedia won't sync the records whose fields were altered.
User avatar
Conor
Top Dog
Posts: 5343
Joined: Sat Jul 03, 2004 12:58 pm
Contact:

Re: Mass text substitution on fields

Post by Conor »

:oops: :shock: You are ahead of me.

Pocketpedia relies on the "date edited" to keep the sync fast.

You can swipe left and delete the collection from Pocketpedia (if you sync more than one Pedia) or delete Pocketpedia and reinstall to have the database sent brand new to Pocketpedia. Otherwise here is the SQL command to update date edited to today.

Code: Select all

update zentry set zdateedited = 563107125.271653 where zurl like "%amazon.com%";
randian
Addicted to Bruji
Addicted to Bruji
Posts: 50
Joined: Tue Jan 14, 2014 8:07 pm

Re: Mass text substitution on fields

Post by randian »

I did "ZDATEEDITED = datetime('now')". Oddly, syncing PocketPedia a second time repeated itself rather than deciding there was nothing to do. Are you storing timestamps as local time rather than UTC? Or is there something wonky with PocketPedia?
User avatar
Conor
Top Dog
Posts: 5343
Joined: Sat Jul 03, 2004 12:58 pm
Contact:

Re: Mass text substitution on fields

Post by Conor »

The function "datetime" does not work, as SQL date is based of January 1st 1970, and the Mac date is based from January 1st 2001. So your 30 years behind, you could add 30 years worth of seconds (978328800).
randian
Addicted to Bruji
Addicted to Bruji
Posts: 50
Joined: Tue Jan 14, 2014 8:07 pm

Re: Mass text substitution on fields

Post by randian »

Conor wrote:The function "datetime" does not work, as SQL date is based of January 1st 1970, and the Mac date is based from January 1st 2001. So your 30 years behind, you could add 30 years worth of seconds (978328800).
That doesn't give you the number range you're showing (563...). It's not clear how that number was generated.
User avatar
Conor
Top Dog
Posts: 5343
Joined: Sat Jul 03, 2004 12:58 pm
Contact:

Re: Mass text substitution on fields

Post by Conor »

Sorry, it would be subtract instead of add to the internal SQL date function.

I copied pasted from a forum post 563107125.271653 = 17.8560098 years. Starting from 2001, would put that at around October 2018.
Post Reply