时间戳

首页 > 解决方案 > SQL模式匹配和替换多个值

sql - SQL模式匹配和替换多个值

问题描述

我有一个包含多个单词的字符串列。如果满足条件，我想使用模式搜索和替换多个值。

输入表：

fruit
Red Apple, Bananas
apples, banana, orange
Strawberry, Strawberries

输出表：

fruit
Red Apple, Banana
Apple, Banana, orange
Strawberry, Strawberry

例如，我想将Apple、apple、分组apples为Apple。

我想过使用函数 REPLACE 但使用此函数，它只允许我搜索 1 个值而不是多个值。

select replace(fruit, 'apples', 'Apple') as fruit 
from table

我也想到了下面的方法，但它也不起作用：

select case when fruit similar to '%(Apple|apple|apples)%' then 'Apple'
            when fruit similar to '%(Banana|banana|bananas)%' then 'Banana'
            when fruit similar to '%(Strawberry|strawberry|strawberries)%' then 'Strawberry'
       end as fruit 
from table

谁能给我一个关于如何解决这个问题的建议？

标签： sqlreplacepattern-matchingamazon-redshift

解决方案

我不知道Amazon Redshift但查看文档我会使用该REGEXP_REPLACE()功能：https ://docs.aws.amazon.com/redshift/latest/dg/REGEXP_REPLACE.html

我会使用pfor PCRE（因为 Perl 引擎是完整且高效的）和可以作为最后一个参数传递的不区分大小写的标志i。

我尝试了 MySQL 8，它也具有该REGEXP_REPLACE()功能但具有不同的参数。这几乎是一样的。似乎我的机器i默认激活了不区分大小写的功能，所以我不需要设置正则表达式标志：

SELECT
`fruit` AS `before`,
REGEXP_REPLACE(
    REGEXP_REPLACE(
        REGEXP_REPLACE(
            REGEXP_REPLACE(`fruit`, 'apples?', 'Apple'),
            'bananas?',
            'Banana'
        ),
        'oranges?',
        'Orange'
    ),
    'strawberr(y|ies)',
    'Strawberry'
) AS `after`
FROM `fruits`

我没有做任何 SQL 更新，只是选择显示之前和之后。

第二个问题是您在同一个水果列表中的多个草莓和草莓条目。这也许可以在第二步中得到纠正。由于水果总是会被替换为带有第一个大写字母的水果的单数版本，因此我们可以捕获水果并查看列表后面是否有相同的水果。带有m 多行标志的示例：

-- Using REGEXP_REPLACE() from MySQL 8 (not totally the same parameters).
SELECT
"Lemon, Lemon, Banana, Orange, Banana
Strawberry, Apple, Banana, Strawberry, Orange
Apple, Blueberry, Blueberry
Strawberry, Apple, Apple, Banana, Orange, Banana
Lemon, Apple, Lemon, Blueberry, Blueberry, Orange" AS `before`,
REGEXP_REPLACE(
    "Lemon, Lemon, Banana, Orange, Banana
Strawberry, Apple, Banana, Strawberry, Orange
Apple, Blueberry, Blueberry
Strawberry, Apple, Apple, Banana, Orange, Banana
Lemon, Apple, Lemon, Blueberry, Blueberry, Orange",
    '(^|,\\s*)([^\\s,]+)(.*?)\\s*,\\s*\\2',
    '$1$2$3',
    1,
    0,
    'm'
) AS `after`

输出是：

Lemon, Banana, Orange
Strawberry, Apple, Banana, Orange
Apple, Blueberry
Strawberry, Apple, Banana, Orange
Lemon, Apple, Blueberry, Orange

在 regex101.com 上使用它。请注意，反斜杠\应该在 SQL 查询中写入两次，因为它必须被转义。

但是正则表达式删除重复项的想法是：

  • 搜索一个水果，[^\s,]+它表示任何不是空格或逗号的字符，至少一次或多次。

  • 然后我在水果前面添加了“以逗号开头或以逗号开头(^|,\s*)” ，以便捕获它并将其放回替换中。

  • 水果后面是我们想要捕获的东西（可能是空的），然后是逗号（周围可能有空格），然后是相同的水果。我们使用正则表达式反向引用编号 2（这是水果）：(.*?)\s*,\s*\2. 以不贪婪的方式搜索水果之后的东西，这样我们就不会走得太远。

但在某些情况下，该模式会匹配一个已经包含一些重复水果的块：Banana, Apple, Apple, Banana. 因此“删除多次出现”可能必须运行不止一次。大概2-3次就够了。

推荐阅读