英文:
How to removing multiple strings from a PostgreSQL string column
问题
UPDATEcandidatesSET"current_ids" = regexp_replace("current_ids", '|ABX1-003|ABX1-006|ABX1-007|', '|', 'g'),"total_ids" = regexp_replace("total_ids", '|ABX1-003|ABX1-006|ABX1-007|', '|', 'g')WHERE"current_ids" ~ '|ABX1-003|ABX1-006|ABX1-007|' OR"total_ids" ~ '|ABX1-003|ABX1-006|ABX1-007|';
这个 SQL 查询会从 "current_ids" 和 "total_ids" 列中删除指定的字符串，并在多个地方执行此操作。如果字符串出现在列中，它们都将被替换成单个的竖线字符 "|".
英文:
In my PSQL database, I have the following table structure and I would like to understand how I can remove multiple strings from the currentIdsand totalIds
CREATE TABLE "public"."candidates" ("day" timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,"study_id" varchar(6) NOT NULL,"site_id" varchar(32),"status" varchar(32) NOT NULL,"total" int4 NOT NULL,"current" int4 NOT NULL,"referrer_token" varchar(255) NOT NULL DEFAULT 'NO_REFERRER_TOKEN'::character varying,"total_ids" text NOT NULL DEFAULT '|'::text,"current_ids" text NOT NULL DEFAULT '|'::text);
What I want to achieve is the following example.
I have for example:
current_ids: '|ABX1-001|ABX1-002|ABX1-003|ABX1-004|ABX1-005|ABX1-006|'total_ids: '|ABX1-001|ABX1-002|ABX1-003|ABX1-004|ABX1-004|ABX1-006|ABX1-007|'
From those 2 columns, I want to remove the IDs: 'ABX1-003', 'ABX1-006', 'ABX1-007'
so the final result will be:
current_ids: '|ABX1-001|ABX1-002|ABX1-004|ABX1-005|'total_ids: '|ABX1-001|ABX1-002|ABX1-004|ABX1-004|'
I tried so far something like this but didn't work
UPDATEcandidatesSET"current_ids" = regexp_replace("current_ids", 'ABX1-003|ABX1-006|ABX1-007', '', 'g')WHERE"current_ids" in('ABX1-003', 'ABX1-006', 'ABX1-007')
答案1
得分: 0
以下查询将仅更新包含将被更改的字符串的行：
WITH patterns(pattern) AS (VALUES ('(?<=\|)(ABX1-003|ABX1-006|ABX1-007)\|'))UPDATE candidatesSET current_ids = regexp_replace(current_ids, patterns.pattern, '', 'g'),total_ids = regexp_replace(current_ids, patterns.pattern, '', 'g')FROM patternsWHERE candidates.current_ids ~ patterns.patternOR candidates.total_ids ~ patterns.pattern;
一个单一的模式用于选择将受影响的行并驱动正则表达式替换。前瞻断言 (?<=\|) 确保每个替换匹配之前有一个 | 分隔符，而不会将 | 包括在匹配中；因此，只会找到完整字段匹配。
英文:
The following query will only update rows with strings that will be changed:
WITH patterns(pattern) AS (VALUES ('(?<=\|)(ABX1-003|ABX1-006|ABX1-007)\|'))UPDATE candidatesSET current_ids = regexp_replace(current_ids, patterns.pattern, '', 'g'),total_ids = regexp_replace(current_ids, patterns.pattern, '', 'g')FROM patternsWHERE candidates.current_ids ~ patterns.patternOR candidates.total_ids ~ patterns.pattern;
A single pattern is used to both select the rows that will be affected and to drive the regular expression replacement. The lookbehind, (?<=\|), insures that each replacement match is preceded by a | delimiter without consuming the | by the match; thus, only full field matches will be found.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论