我想尽可能多地重复这个命令sometext
在该领域note
(表中的几行itemNotes
可以有一个或多个sometext
在该领域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 https://stackoverflow.com/a/50306716/3154274但我很难将我想要的内容与此查询集成:
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
(e.g. sometext
, SOMEtext
, SOmeText
...)比照这个question https://stackoverflow.com/a/56243026/3154274
当前输入和所需输出:
对于单行,注释字段可能看起来像(并且表中的许多行itemNotes
可能看起来像这样):
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 https://github.com/zotero/zotero/blob/26056c87f1d0b31dc56981adaabcab8fc2f85294/resource/schema/userdata.sql文件(第 85 行)。该表是由该查询创建的
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
);