Sqlite: multiple update (find and replace) case insensitive

Question

I use DB Browser for SQLite to visualize and update an sqlite file.

I am able to do run a case sensitive query to update some text like this:

UPDATE itemNotes  SET note = REPLACE(note ,  'sometext', 'abc');

But I would like to match replace all case combinations of sometext (e.g. sometext, SOMEtext, SOmeText...)

I tried to do this :

UPDATE itemNotes  SET note = REPLACE(LOWER(note),  'sometext', 'abc');

But this transform the whole content of the field note in lower case which isn't what I want.

I also tried these query but without success:

UPDATE itemNotes  SET note = REPLACE(note, BINARY 'sometext', 'abc')
UPDATE itemNotes  SET note = REPLACE(note, COLLATE Latin1_General_CS_AS'sometext', 'abc')

I am doing it on the zotero.sqlite, which is created by this file (line 85). The table is created by this query

CREATE TABLE itemNotes (
    itemID INTEGER PRIMARY KEY,
    parentItemID INT,
    note TEXT,
    title TEXT,
    FOREIGN KEY (itemID) REFERENCES items(itemID) ON DELETE CASCADE,
    FOREIGN KEY (parentItemID) REFERENCES items(itemID) ON DELETE CASCADE
);

Please provide a tailored toy database, i.e. a few lines of "create ..." and "insert ...". Consider using the `.dump` from commandline tool. — Yunnosch, May 21 '19 at 16:18
@Yunnosch, thanks for your help, please see the edit in my question — MagTun, May 21 '19 at 16:33

Oleg Imanilov · Accepted Answer · 2019-05-21T20:38:58.953

1

You need to search substring in LOWER(note), but replace it in original. I don't know where you getting sometext from, assume that you can check it's length. In following example I'll use constant.

UPDATE itemNotes  
SET 
  note = SUBSTR(note, 0, INSTR(LOWER(note), 'sometext')) || 'abc' || SUBSTR(note, INSTR(LOWER(note), 'sometext')+sometext_len)
WHERE 
  INSTR(LOWER(note), 'sometext') >= 0;

!Note: It will work only for one replace at a time.

edited May 21 '19 at 20:38

answered May 21 '19 at 16:51

Oleg Imanilov

2,268
1
11
21

Thanks for this but I think you missed the closing `(` of the first `SUBSTR`. Is is just before `|| 'abc'` or at the end of the line? – MagTun May 21 '19 at 18:39

score 0 · Answer 2 · answered May 28 '19 at 20:05

Nosyara answer works great but it only removes 1 instance at a time.

I also tried this from here:

UPDATE itemNotes  
SET 
  note = SUBSTR(note, 0, INSTR(LOWER(note), 'sometext')) || 'abc' || SUBSTR(note, INSTR(LOWER(note), 'sometext')+sometext_len)
WHERE 
  note LIKE "%sometext%";

But when I have many occurence of sometext in a field note, then only the first on is removed. Based on a comment on this question, I ended up using python to replace the sqlite, see here

Sqlite: multiple update (find and replace) case insensitive

2 Answers2

Linked