Repeat a command while true or x times (equivalent of while/for loop)

Question

I would like to repeat this command as many times as there is still sometextin the field note (several rows from the table itemNotes could have one or more sometext in the field note):

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;

So a proto-code would be :

While (SELECT * FROM itemNotes  WHERE note like "%sometext%") >1

    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;
END

But apparently Sqlite3 doesn't support While loop or for loop. They can be emulated with something like this but I have difficulties integrating what I want with this query:

    WITH b(x,y) AS 
    (
        SELECT 1,2 
        UNION ALL 
        SELECT x+ 1, y + 1 

    FROM b 
    WHERE x < 20
) SELECT * FROM b;

Any idea how to do this?

PS: I don't use replace because I want to replace all the case combinations of sometext (e.g. sometext, SOMEtext, SOmeText...) cf this question

Current input and desired output:

For a single row, a note field could look like (and many rows in the table itemNotescould look like this one):

There is SOmetext and also somETExt and more SOMETEXT and even more sometext

The query should output:

There is abc and also abc and more abc and even more 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
);

Sorry I didn't mention this: it's because I want to match all the case combination of `sometext` cf https://stackoverflow.com/a/56243026/3154274 — MagTun, May 28 '19 at 11:50
@CaiusJard, I tried this `UPDATE itemNotes SET note = REPLACE(note , 'sometext', 'abc') COLLATE NOCASE;` but this query only replace the lowercase occurence of sometext. — MagTun, May 28 '19 at 12:25

Stephan · Accepted Answer · 2019-05-28T13:27:54.663

1

You just have your answer in your query:

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

It will update all rows that contain sometext in the note field

UPDATE

If you want to update the field which has multiple occurrences in different cases and maintain the rest of the text the simplest solution imo is to use regex and for that you need an extension

UPDATE itemNotes  
SET 
  note = regex_replace('\bsometext\b',note,'abc')
WHERE 
  note LIKE "%sometext%";

edited May 28 '19 at 13:27

answered May 28 '19 at 12:07

Stephan

8,000
3
36
42

Thanks for this but your query only replace the first instance in each row. However, I have many `sometext` in each row. – MagTun May 28 '19 at 12:16
@MagTun you mean you have multiple occurrences of `sometext` in the `note` field? – Stephan May 28 '19 at 12:20
@MagTun it will be helpful if you added and example : input and desire output – Stephan May 28 '19 at 12:21
Yes. To be sure, in the table `itemNotes` I could have 100 rows that each have 50+ `sometext` in the field `note`. – MagTun May 28 '19 at 12:21
Stephan, I have add an example of input and output in my question. Sorry for not being more precise from the beginning – MagTun May 28 '19 at 12:37
1

@MagTun my advice is that if u cannot make the extension work just do the replace in any scripting language like : php, python, bash etc – Stephan May 28 '19 at 12:58
Thanks Stephan, but why such a basic feature is so difficult (or as you imply impossible) to do in Sqlite3? – MagTun May 28 '19 at 13:01
@MagTun well its simple sqllite its a lightweight db used to do simple things so it makes sense to not have advance features like this one if you need them then just use mysql :) which has a replace regex function built in – Stephan May 28 '19 at 13:25

score 1 · Answer 2 · answered May 28 '19 at 20:02

As recommended by Stephan in his last comment, I used python to do this.

Here is my code :

import sqlite3
import re
keyword = "sometext"
replacement = "abc"

db = sqlite3.connect(path_to_sqlite)
cursor = db.cursor()

cursor.execute(f'SELECT * FROM itemNotes  WHERE note like "%{keyword}%"')

for row in cursor.fetchall():
    row_regex = re.compile(re.escape(keyword), re.IGNORECASE)
    row_regex_replaced = row_regex.sub(replacement, row[2])

    rowID = row[0]
    sql = "REPLACE INTO itemNotes (itemID,note) VALUES (?,?)"
    data = (rowID, row_regex_replaced)
    cursor.execute(sql, data)
    db.commit()

Repeat a command while true or x times (equivalent of while/for loop)

2 Answers2

Linked