重复一个命令 while true 或 x 次(相当于 while/for 循环)

问题描述

我想多次重复这个命令，因为 note 字段中仍然有 sometext(表格中的几行 itemNotes在 note) 字段中可以有一个或多个 sometext:

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;

所以一个原型代码是:

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

但显然 Sqlite3 不支持 While 循环或 for 循环.它们可以用类似 this 的东西来模拟，但我很难将我想要的东西与这个查询结合起来:

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;

知道怎么做吗?

PS:我不使用 replace 因为我想替换 sometext 的所有大小写组合(例如 sometext, SOMEtext, SOmeText...) 参考这个问题

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

当前输入和期望输出:

对于单行，注释字段可能如下所示(表格中的许多行itemNotes可能看起来像这样):

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

查询应该输出:

There is abc and also abc and more abc and even more abc

我是在 zotero.sqlite 上做的，它是由 this 文件(第 85 行).该表由此查询创建

---

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

推荐答案

您只需在查询中找到答案:

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%";

它将更新 note 字段中包含 sometext 的所有行

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

更新

如果你想更新在不同情况下多次出现的字段并维护文本的其余部分，最简单的解决方案imo是使用regex，为此你需要一个扩展

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%";

这篇关于重复一个命令 while true 或 x 次(相当于 while/for 循环)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！