Bear: Batch Editing Notes

Writing
, ,

Batch editing Bear notes on macOS requires essentially two steps:

  1. Executing SQLite queries on Bear’s database, which is located at ~/Library/Group Containers/9K33E3U3T4.net.shinyfrog.bear/Application Data/database.sqlite[1].
  2. Force syncing local changes to iCloud.

Executing SQLite queries

Here is a list of tooling options for SQLite queries:

Once the database is properly loaded, look for column ZTEXT in table ZSFNOTE, where the content of notes is stored. Execute the queries below as needed.

Find notes with duplicate content

1
SELECT ZTITLE, ZTEXT, count(*) COUNT FROM ZSFNOTE GROUP BY ZTEXT HAVING COUNT > 1;

Find %TEXT%

1
2
3
SELECT ZTEXT, instr(ZTEXT,'%TEXT%')
FROM ZSFNOTE
WHERE INSTR(ZTEXT,'%TEXT%')>0;

Replace %ORIG% with %REPL%

1
2
3
4
5
UPDATE ZSFNOTE SET ZTEXT = REPLACE(ZTEXT, '%ORIG%', '%REPL%');

-- Find `%REPL%` instances after replacement
SELECT ZTEXT FROM ZSFNOTE
WHERE INSTR(ZTEXT, '%REPL%')> 0;

Advanced find and replace

  1. The documentation of SQLite core functions is available here.[2]
  2. Wildcards can be used, following the LIKE operator, where % and _ are the equivalent of * and . in regular expression.
1
2
3
4
5
6
7
8
9
10
11
12
/* Here, `substr(ZTEXT, 1, 6)` returns the first 6 characters
of `ZTEXT` as defined by the `WHERE` clause. */
UPDATE ZSFNOTE SET ZTEXT = REPLACE(ZTEXT, substr(ZTEXT, 1, 6), '%REPL%')

/* Here, the `WHERE` clause returns any patterns in `ZTEXT`
that begin with 'OR' and end with 'G'
with a single character in between. */
WHERE ZTEXT LIKE '%OR_G%';

-- Find `%REPL%` instances after replacement
SELECT ZTEXT FROM ZSFNOTE
WHERE INSTR(ZTEXT, '%ORIG%')> 0;

Force syncing to iCloud

This is where things get a bit hacky. As Bear only syncs when changes are detected in ZTEXT, operations such as pinning / unpinning, trashing / restoring, archiving / unarchiving do not effect syncing, as they only update columns ZPINNEDDATE, ZTRASHEDDATE or ZARCHIVEDDATE.

Instead, use tagging / untagging, since tags are store in ZTEXT, and their changes are treated as updates to ZTEXT.

Here are the steps:

  1. In Bear, create a temporary tag, e.g. #__stage__#.
  2. Select the notes to be synced, drag them onto #__stage__# which should now be at the top of the left panel.
  3. Wait for syncing to complete, then delete #__stage__#.

Drag and Drop
[Drag and Drop]

  1. Bear. Where are Bear’s notes located. ↩︎

  2. For practical examples, refer to w3resource’s SQLite Core Functions. ↩︎