Sqlite:多次更新(查找和替换)不区分大小写 [英] Sqlite: multiple update (find and replace) case insensitive

问题描述

我使用 DB Browser for SQLite 来可视化和更新 SQLite 文件.

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

但我想匹配替换 sometext 的所有大小写组合(例如 sometext、SOMEtext、SOmeText...)

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

我尝试这样做:

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

但是这将字段 note 的整个内容转换为小写，这不是我想要的.

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

我是在 zotero.sqlite 上做的，它是由这个 file(第 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
);

推荐答案

LOWER(note) 中需要搜索子串，但替换为原始的.我不知道你从哪里得到一些文本，假设你可以检查它的长度.在以下示例中，我将使用常量.

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.

这篇关于Sqlite:多次更新(查找和替换)不区分大小写的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！