Oracle SQL 正则表达式 匹配两个字段

Oracle SQL Regex Matching two fields

提问人：Teli 提问时间：9/22/2023 最后编辑：Teli 更新时间：9/23/2023 访问量：54

问：

我在 oracle sql 中有两个表，每个表中都有一个字段，如果不匹配，我需要匹配并显示。单独表中的两个字段具有不同的格式化方式，这并不理想，但情况确实如此。例：

表 1：

编号 EXAMPLE_STRING EXAMPLE_REGEX_MATCH
1 （0888DY：A001：B2：C3） 货号： A1B2C3
2 （0888DY：A001：B3） 答1B3

表2：

编号 EXAMPLE_STRING EXAMPLE_REGEX_MATCH
1 0888DY，A1：B2：C3 货号： A1B2C3
2 0888DY，A1：B2 答1B2

在上面的例子中，我希望 ID 1 在表之间匹配并被忽略，并且选择 ID 2，因为它彼此不匹配。匹配的确定只是 A 后跟非零，B 后跟任何非零，C 后跟任何非零，依此类推，因此在 ID 1 的情况下：A1B2C3。理想情况下，前缀 0888DY、逗号、括号、前导零等其他所有内容都将被忽略。我假设我需要使用正则表达式来做到这一点，所以这是我的开始：

SELECT a.ID, a.EXAMPLE_STRING, b.EXAMPLE_STRING
FROM Table1 a
INNER JOIN Table2 b
ON a.ID = b.ID
WHERE REGEX_REPLACE(a.EXAMPLE_STRING, regexhere) != REGEX_REPLACE(b.EXAMPLE_STRING, regexhere)
SQL 正则表达式 Oracle

评论

0赞 Isolated 9/22/2023
前缀对匹配或非匹配逻辑也很重要吗？0888DY
0赞 Barmar 9/22/2023
你不能用于这个。它返回正则表达式匹配的位置。这对于判断两个字符串中的模式匹配是否相同没有用处。REGEXP_INSTR()
0赞 Teli 9/22/2023
“0888DY前缀对匹配逻辑或非匹配逻辑也很重要吗？”不。只有字母 A-C 和它们后面的数字必须匹配。我只是无法让正则表达式忽略除匹配变量之外的所有内容。

答：

0赞 Barmar 9/22/2023 #1

REGEXP_INSTR()对于判断两个字符串是否匹配相同的模式没有用处。它只是返回找到模式匹配的位置（就像使用纯字符串一样）。INSTR()

为了变成，你需要与捕获组一起使用，这样你就可以删除前导零。A001A1REGEXP_REPLACE()

您还需要在第一个之前或比较之前删除所有内容。:,

WHERE REGEXP_REPLACE(REGEXP_REPLACE(a.EXAMPLE_STRING, '^[^,:]*', ''), '[,:]([[:alpha:]])0*([[:digit:]]+)', '\1\2') != REGEXP_REPLACE(REGEXP_REPLACE(b.EXAMPLE_STRING, '^[^,:]*', ''), '[,:]([[:alpha:]])0*([[:digit:]]+)', '\1\2')
0赞 M. Pour 9/22/2023 #2

首先，你提到的匹配模式......我不知道为什么我不能理解它。你描述的方式，

匹配的确定只是 A 后跟一个非零 B 后跟任何非零，C 后跟任何非零

这也不应与表 1 的第 1 行匹配，因为 A 后面跟着一些零，但在表 2 中情况并非如此。

因此，如果这个答案对您没有帮助，我需要更多的解释。

正如你提到的：

单独表中的两个字段具有不同的存在方式 格式化，这并不理想

一个想法是尝试对这两个字段进行规范化，以便那些彼此不匹配的字段。看看这个：

-- CTEs to normalize the strings 
--and REGEXP_REPLACE to remove all non-alphanumeric characters from EXAMPLE_STRING
WITH NormalizedTable1 AS (
  SELECT ID, REGEXP_REPLACE(EXAMPLE_STRING, '[^A-Za-z0-9]', '') AS NormalizedString
  FROM Table1
),
NormalizedTable2 AS (
  SELECT ID, REGEXP_REPLACE(EXAMPLE_STRING, '[^A-Za-z0-9]', '') AS NormalizedString
  FROM Table2
)
-- Finally, join the normalized tables and select non-matching records
SELECT t1.ID, t1.EXAMPLE_STRING AS Table1String, t2.EXAMPLE_STRING AS Table2String
FROM NormalizedTable1 t1
INNER JOIN NormalizedTable2 t2 ON t1.ID = t2.ID
WHERE t1.NormalizedString != t2.NormalizedString;

您应该非常谨慎地选择正则表达式进行规范化，以避免意外。

正如我所说，我无法真正理解您问题的那部分，因此我的正则表达式可能无法完全解决您的问题

祝你好运！

评论

0赞 Teli 9/22/2023
感谢您的回复。这非常接近我想要的。从本质上讲，我希望规范化/匹配只在字母 A-C 上（无论前导零和前缀如何）和它之后但在下一个字母之前的非零数字上。（0888DY：A001：B2：C3） 转到 A1B2C3 （0888DY：A001：B3） 转到 A1B3 0888DY，A1：B2：C3 转到 A1B2C3 0888DY，A1：B2 转到 A1B2 获取正则表达式很棘手。很抱歉造成混乱。
0赞 M. Pour 9/22/2023
是的，它不是一个简单的正则表达式。我可能会专注于将其分为两部分：首先删除不需要的字符并保留 A、B、C 和数字，然后删除前导零。
0赞 d r 9/23/2023 #3

根据提供的数据以及您在 M. Pour 的回答之后的评论（“本质上，我希望规范化/匹配只在字母 A-C 上（无论前导零和前缀）和它后面但在下一个字母之前的非零数字”），一种选择可能根本不使用正则表达式。
创建 CTE，用于转换示例数据以使其模式化：

WITH        --  Sample Data
    tbl_1 (ID, EXAMPLE_STRING) AS
        (   Select  1,  '(0888DY:A001:B2:C3)' From Dual Union All
        Select  2,  '(0888DY:A001:B3)' From Dual 
        ),
    tbl_2 (ID, EXAMPLE_STRING) AS
        (   Select  1,  '0888DY,A1:B2:C3' From Dual Union All
        Select  2,  '0888DY,A1:B2' From Dual 
        ),
  transformed AS
    (   Select        t1.ID, 
                    t1.EXAMPLE_STRING "T1_STRING_ORIGINAL",
                    t2.EXAMPLE_STRING "T2_STRING_ORIGINAL",
                    Replace(SubStr( Replace(Replace(Replace(t1.EXAMPLE_STRING, '(', ''), ')', ''), ',', ':')
                          , InStr(Replace(Replace(Replace(t1.EXAMPLE_STRING, '(', ''), ')', ''), ',', ':') || ':', ':', 1, 1) + 1 ), ':', '') "T1_TO_MATCH",  
                    -- 
                    Replace(SubStr( Replace(Replace(Replace(t2.EXAMPLE_STRING, '(', ''), ')', ''), ',', ':')
                          , InStr(Replace(Replace(Replace(t2.EXAMPLE_STRING, '(', ''), ')', ''), ',', ':') || ':', ':', 1, 1) + 1 ), ':', '') "T2_TO_MATCH"
        From            tbl_1 t1
        Inner Join  tbl_2 t2 ON(t2.ID = t1.ID)
    ),
--
--  Result for transformed CTE
--
--          ID T1_STRING_ORIGINAL  T2_STRING_ORIGI T1_TO_MATCH    T2_TO_MATCH      
--  ---------- ------------------- --------------- -------------- -----------------
--           1 (0888DY:A001:B2:C3) 0888DY,A1:B2:C3 A001B2C3       A1B2C3           
--           2 (0888DY:A001:B3)    0888DY,A1:B2    A001B3         A1B2

注意：上面的代码删除了进一步处理不需要的字符，例如圆括号和逗号，并切断了第一个元素。请注意，这适用于提供的示例数据，如果真实数据中还有其他一些此类字符，则可能还需要处理它们。
生成的数据集应该使用另一个 CTE 进行更多调整，然后才能获得预期的结果

  matches AS
    (   Select  t.ID, T1_STRING_ORIGINAL, T2_STRING_ORIGINAL, 
                Case When InStr(t.T1_TO_MATCH, 'A') > 0 Then SubStr( t.T1_TO_MATCH, 1, InStr(t.T1_TO_MATCH, 'A')) End|| 
                  Case When InStr(t.T1_TO_MATCH, 'A') > 0 Then CAST(SubStr(t.T1_TO_MATCH, InStr(t.T1_TO_MATCH, 'A') + 1, Case When InStr(t.T1_TO_MATCH, 'B') > 0 Then InStr(t.T1_TO_MATCH, 'B') Else 9999 End - InStr(t.T1_TO_MATCH, 'A') - 1) as Number DEFAULT 0 ON CONVERSION ERROR) End ||
                Case When InStr(t.T1_TO_MATCH, 'B') > 0 Then SubStr( t.T1_TO_MATCH, InStr(t.T1_TO_MATCH, 'B'), 1) End || 
                  Case When InStr(t.T1_TO_MATCH, 'B') > 0 Then CAST(SubStr(t.T1_TO_MATCH, InStr(t.T1_TO_MATCH, 'B') + 1,  Case When InStr(t.T1_TO_MATCH, 'C') > 0 Then InStr(t.T1_TO_MATCH, 'C') Else 9999 End - InStr(t.T1_TO_MATCH, 'B') - 1) as Number DEFAULT 0 ON CONVERSION ERROR) End || 
                Case When InStr(t.T1_TO_MATCH, 'C') > 0 Then SubStr( t.T1_TO_MATCH, InStr(t.T1_TO_MATCH, 'C'), 1) End || 
                  Case When InStr(t.T1_TO_MATCH, 'C') > 0 Then CAST(SubStr(t.T1_TO_MATCH, InStr(t.T1_TO_MATCH, 'C') + 1) as Number DEFAULT 0 ON CONVERSION ERROR)  End
                "T1_TO_MATCH",
                --
                Case When InStr(t.T2_TO_MATCH, 'A') > 0 Then SubStr( t.T2_TO_MATCH, 1, InStr(t.T2_TO_MATCH, 'A')) End|| 
                  Case When InStr(t.T2_TO_MATCH, 'A') > 0 Then CAST(SubStr(t.T2_TO_MATCH, InStr(t.T2_TO_MATCH, 'A') + 1,  Case When InStr(t.T2_TO_MATCH, 'B') > 0 Then InStr(t.T2_TO_MATCH, 'B') Else 9999 End - InStr(t.T2_TO_MATCH, 'A') - 1) As Number DEFAULT 0 ON CONVERSION ERROR) End ||
                Case When InStr(t.T2_TO_MATCH, 'B') > 0 Then SubStr( t.T2_TO_MATCH, InStr(t.T2_TO_MATCH, 'B'), 1) End || 
                  Case When InStr(t.T2_TO_MATCH, 'B') > 0 Then CAST(SubStr(t.T2_TO_MATCH, InStr(t.T2_TO_MATCH, 'B') + 1,  Case When InStr(t.T2_TO_MATCH, 'C') > 0 Then InStr(t.T2_TO_MATCH, 'C') Else 9999 End - InStr(t.T2_TO_MATCH, 'B') - 1) as Number DEFAULT 0 ON CONVERSION ERROR) End || 
                Case When InStr(t.T2_TO_MATCH, 'C') > 0 Then SubStr( t.T2_TO_MATCH, InStr(t.T2_TO_MATCH, 'C'), 1) End || 
                  Case When InStr(t.T1_TO_MATCH, 'C') > 0 Then CAST(SubStr(t.T2_TO_MATCH, InStr(t.T2_TO_MATCH, 'C') + 1) as Number DEFAULT 0 ON CONVERSION ERROR) End
                "T2_TO_MATCH"
         From   transformed t 
    )    
--
--  Result for matches CTE
--
--          ID T1_STRING_ORIGINAL  T2_STRING_ORIGI T1_TO_MATCH          T2_TO_MATCH         
--  ---------- ------------------- --------------- -------------------- --------------------
--           1 (0888DY:A001:B2:C3) 0888DY,A1:B2:C3 A1B2C3               A1B2C3              
--           2 (0888DY:A001:B3)    0888DY,A1:B2    A1B3                 A1B2     

--  M a i n   S Q L           
SELECT  ID, T1_STRING_ORIGINAL, T2_STRING_ORIGINAL
FROM    matches
WHERE T1_TO_MATCH != T2_TO_MATCH
--
--  R e s u l t :
--
--          ID T1_STRING_ORIGINAL  T2_STRING_ORIGINAL
--  ---------- ------------------- ------------------
--           2 (0888DY:A001:B3)    0888DY,A1:B2

如果可以调整它以处理您的实际数据 - 它应该比正则表达式快得多。

上一个：错误：正则表达式无效：postgresql中的字符范围无效

下一个：regexp_substr 使用 % 拾取类似于 like condition 的单词