主页 / dba / 问题 / 313122
Accepted
gabtzi
Asked: 2022-06-09 13:23:25 +0800 CST

提取分隔符可能多次出现的子字符串

  • 772

我有一列带有这种模式的字符串<email> - <id>。电子邮件始终是第一个字符串。

我想只提取电子邮件地址，但这里的问题是电子邮件地址也可以包含连字符，所以我不能确定分隔符只会出现一次。

所以基本上我想匹配 .* 直到最后一个连字符并将其提取为电子邮件。

嗯，这不完全是关于管理，它是关于编写一个查询来提取数据，所以它在数据挖掘领域，但是这个论坛完全与数据库相关，所以我认为它比 stackoverflow 更合适。

我只尝试过，SUBSTRING_INDEX()但最终得到了不好的结果。

这是一个生产系统，所以我不能真正干预设计，因此需要提取信息。

mariadb mariadb-10.4

3 个回答

  1. Best Answer

    这里有一些可能性 -解决方案1使用标准的 MariaDB字符串函数，而解决方案2使用regular expressions（regexes - 优秀网站在这里，快速开始在这里）。您还可以使用GENERATED列让您的生活更轻松。

    方案一（使用普通的 MySQL/MariaDB 字符串函数）：

    如果您确定您的数据是干净的，并且每个字段都以<+ email +开头> - <.... more stuff...，您可以执行以下操作（解决方案 1 的所有代码都可以在此处的小提琴中找到）：

    CREATE TABLE test_ter
(
  field VARCHAR (200) NOT NULL
);

    数据：

    INSERT INTO test_ter VALUES
('<[email protected]> - <1345>'),
('<[email protected]> - <1345>'),
('<rubbish> - <[email protected]> - <1345>'),
('<more_rubbish> - <[email protected]> - <1345>'),
('<more stuff> - <[email protected] - <34343>');

    然后，我们运行：

    SELECT
  field, 
  INSTR(field, '> - <') AS instr, 
  POSITION('> - <' IN field) AS pos, 
  LOCATE('> - <', field) AS loc,
  SUBSTRING_INDEX(field, '> - <', 1) AS substr
FROM
  test_ter;

    结果：

    field                                       instr pos loc   substr
<[email protected]> - <1345>                        14  14   14  <[email protected]
<[email protected]> - <1345>                        14  14   14  <[email protected]
<rubbish> - <[email protected]> - <1345>             9   9    9  <rubbish
<more_rubbish> - <[email protected]> - <1345>   14  14   14  <more_rubbish
<more stuff> - <[email protected] - <34343>  12  12   12  <more stuff

    我们可以看到这SUBSTRING_INDEX()让我们最接近我们想要的答案 - 否则我们将不得不使用更多嵌套函数来获得我们想要的结果 - 请参阅此答案的先前编辑。

    我们结合SUBSTING_INDEX()函数TRIM()得到我们的答案：

    SELECT 
  TRIM(LEADING '<' FROM SUBSTRING_INDEX(field, '> - <', 1))
FROM                                                                  
  test_ter;

    结果：

    TRIM(LEADING '<' FROM SUBSTRING_INDEX(field, '> - <', 1))
[email protected]
[email protected]
rubbish
more_rubbish
more stuff

    根据您的输入数据的干净程度（我假设一些无效的电子邮件 - 最基本的检查是字符串是否包含@符号。

    我们可以将其与使用GENERATED列结合起来，您可以在任何位到达磁盘之前进行大量检查，如下所示：

    ALTER TABLE test_ter
ADD COLUMN email VARCHAR (200)
GENERATED ALWAYS AS
(
  CASE
    WHEN 
      INSTR(TRIM(LEADING '<' FROM SUBSTRING_INDEX(field, '> - <', 1)), '@') = 0 
        THEN NULL
    ELSE
      TRIM(LEADING '<' FROM SUBSTRING_INDEX(field, '> - <', 1))
  END
);

    并检查：SELECT * FROM test_ter;- 结果：

    field                                          email
<[email protected]> - <1345>                        [email protected]
<[email protected]> - <1345>                        [email protected]
<rubbish> - <[email protected]> - <1345>            NULL
<more_rubbish> - <[email protected]> - <1345>   NULL
<more stuff> - <[email protected] - <34343>  NULL

    因此，我们可以看到第一对中不包含电子邮件的记录< >被认为是NULL- 但如果您很高兴您的输入是干净的，那么这是不必要的。

    如果合适，您还可以在GENERATED字段上使用索引来加快搜索速度：

    CREATE INDEX tt_email_ix ON test_ter (email);

    另一个答案提出了 the<和 the>只是占位符并且您的数据在表单中的可能性[email protected] - stuff....，那么您所需要的只是类似

    ...
SUBSTRING_INDEX(field, ' ', 1) -- 1 space, or use 1 space and a hyphen ' -`
...

    这将截断字符串，只留下电子邮件（见小提琴）。

    解决方案 2（使用正则表达式）：

    您可以执行以下操作（解决方案 2 的所有代码都可以在此处的小提琴中找到）：

    CREATE TABLE test
(
  field VARCHAR (200) NOT NULL
);

    填充一些示例数据：

    INSERT INTO test VALUES
('<[email protected]> - <1345>'),
('<[email protected]> - <1345>'),
('<rubbish> - <[email protected]> - <1345>'),
('<more_rubbish> - <[email protected]> - <1345>');

    然后运行（使用regular expression- 正则表达式）：

    SELECT 
  REGEXP_SUBSTR
  (
    field, 
    '[A-Z][A-Z0-9._-]+@[A-Z0-9_-]+\.[A-Z]{2,4}'
  ) AS email
FROM 
  test;

    结果：

    email
[email protected]
[email protected]
[email protected]
[email protected]

    现在，我用于电子邮件的简单[A-Z][A-Z0-9._-]+@[A-Z0-9_-]+\.[A-Z]{2,4}正则表达式是- 您可以根据需要/要求将其复杂化 - 请参阅此处- 链接到的一个正则表达式解决方案有 6,500 个字符，也许是矫枉过正？搜索将使您在解决方案健壮和适合您之间做出妥协。

    正则表达式解释（可以在这里找到一个关于正则表达式的优秀网站，快速开始在这里）：

    • [A-Z]

      必须以单个字母开头 - 即A-Z或a-z。根据这里不太正确- 但这只是一个简单的第一个近似值。在 MySQL/MariaDB 中，只会[A-Z]使用默认的不区分大小写的排序规则。

    • [A-Z0-9._-]+

      符号之前的电子邮件的其余部分@- 匹配字符A-Z，a-z或者匹配._-一次或多次（+“元字符”指定了这一点 - 请参阅快速入门 - 元字符在正则表达式中具有特殊含义），

      方括号[并]包含所谓的字符类或字符集- 请参阅上面的快速入门链接，

    • @匹配文字“at”符号，

    • 更多的字母、数字和_-站点名称，

    • \.匹配文字点（句号或句点 - 即.字符）。用.反斜杠 ( \) 转义，因为点也是一个元字符 - 未转义它代表任何单个字符 - 如_SQL 中的下划线 ( )，

    • [A-Z]{2,4}域名 - 匹配出现 2、3 或 4 次的字母 [AZ]（和 [az]） - 即.fr，.com或info例如。

      花括号 ( {, }) 用于指定重复次数。如果您只有{3}，那将意味着您想要的模式仅出现 3 次和 3 次。

    请注意，正则表达式很昂贵，并且根据您的表大小和字符串的长度，您的查询可能会很慢。您可以使用 s 以牺牲一点磁盘空间为代价来减少查询时间成本，GENERATED COLUMN如下所示：

    CREATE TABLE test_bis
(
  field VARCHAR (200) NOT NULL,
  email VARCHAR (200) AS
  (
    REGEXP_SUBSTR
    (
      field, 
      '[A-Z][A-Z0-9._-]+@[A-Z0-9._-]+\.[A-Z]{2,4}'
    )
  ) PERSISTENT -- HDD cost,  also works with VIRTUAL - CPU cost.
);

    做了同样的INSERT事情 - 见小提琴，结果是：

    field                                         email
<[email protected]> - <1345>                       [email protected]
<[email protected]> - <1345>                       [email protected]
<rubbish> - <[email protected]> - <1345>           [email protected]
<more_rubbish> - <[email protected]> - <1345>  [email protected]

    您可以索引此PERSISTENT字段以加快搜索速度：

    CREATE INDEX fb_regex_email
ON test_bis (email);

    据我所知，MariaDB 还没有功能（或表达式）索引（例如，参见PostgreSQL ）。

    如果您不想牺牲 HDD 空间，您可以改为创建GENERATED列VIRTUAL- 以 CPU 周期为代价 - à vous le choix！我无法测试索引，因为示例表非常小，以至于 MySQL 无论如何都会进行表扫描，无论索引是否存在。

    我只是建议您使用自己的硬件和自己的数据测试这些解决方案，以确保您的性能对于您的要求/约束​​是最佳的。

    • 2

  2. 向后执行：搜索子字符串，直到字符串末尾的连字符（使用SUBSTRING_INDEX函数），然后TRIM从值中找到找到的部分。

    如果分隔符严格如图所示（-连字符前后有空格），则将其用作三字符分隔符。-秋名

    例子

    CREATE TABLE test
(
    field varchar (200) NOT NULL
);

INSERT INTO test VALUES
('[email protected] - 1234'),
('[email protected] - 5678'); 

SELECT
    TRIM(TRAILING 
        CONCAT(' - ', 
            SUBSTRING_INDEX(field, ' - ', -1))
        FROM field) AS email
FROM test;
    电子邮件
    [email protected]
    [email protected]

    db<>小提琴演示

    • 1

  3. 我有一列具有这种模式的字符串-

    这就是你最大的问题。
    您在一个字段中有两位数据，这从根本上是个坏主意。

    在决定如何存储任何数据之前，您应该问的第一个问题是

    我将如何访问这些数据？

    你真的应该在两个单独的字段中拥有它，然后这个“提取”问题就会“消失”。

    数据库非常非常擅长查找少量数据并将它们组合在一起。
    他们通常在寻找大量数据并将它们分开时非常垃圾。

    • -1

相关问题