如何在PATINDEX中执行OR

Question

我想使用PATINDEX来匹配列表中的任何项。如果patindex接受正则表达式，我想做一些类似以下的事情：

select patindex('% for | is| my%','just for stackoverflow')

显然，这不起作用，如果没有匹配，则返回0。是否存在可与LIKE/PATINDEX一起使用的或语法

Answer 1

你可以用到这样的东西。

SELECT t.SearchString, 
        FirstOccurance = MIN(NULLIF(PATINDEX(ss.value, t.SearchString), 0))
FROM (VALUES('just for stackoverflow'), ('no match')) AS t (SearchString)
    CROSS APPLY STRING_SPLIT('%for%|%is%|%my%', '|') AS ss
GROUP BY t.SearchString;

它基本上将所有搜索词作为单个字符串传递，将它们拆分，每个搜索词执行一次PATINDEX，然后返回最低值(不包括被NULLIF删除的0)。

如果你的搜索词不是一个单一的字符串，那么一个更好的选择可能是将它们放在一个表中，并使用它执行类似的逻辑，例如

DECLARE @T TABLE (SearchTerm NVARCHAR(MAX))
INSERT @T VALUES ('%for%'), ('%is%'), ('%my%');


SELECT t.SearchString, 
        FirstOccurance = (SELECT TOP 1 PATINDEX(ss.SearchTerm, t.SearchString)
                            FROM @T AS ss
                            WHERE PATINDEX(ss.SearchTerm, t.SearchString) > 0
                            ORDER BY PATINDEX(ss.SearchTerm, t.SearchString))
FROM (VALUES('just for stackoverflow'), ('no match')) AS t (SearchString);

我毫无理由地使用了一种稍微不同的技术(相关子查询中的TOP 1，而不是带有MIN的CROSS APPLY )，只是为了说明有多种方法可以接近它。

Answer 2

当你使用Azure Synapse Analytics时，如果你需要RegEx功能，那么你可以使用Synapse Notebook获得它们。我正在使用的一种模式是，在传统SQL中不可能实现的事情，然后将数据复制到Spark，处理它，然后再放回去。这是一个模式：

​

​

由于存在复制数据的开销、运行Spark池的重复成本、池可能需要几分钟的预热时间，因此我将此模式保留为在传统SQL中不可能实现的情况，或者存在特定需求或已证实的性能优势。

示例数据帧：

​

​

Scala中的一个简单示例：

// Get the data from the dedicated SQL pool
val df = spark.read.synapsesql("someDb.dbo.someTable")

// Add the new columns, and remove any unnecessary ones
val df2 = df.
    withColumn("for_or_in", regexp_extract($"errorMessage", "for|in", 0)).
    withColumn("for_or_in_with_word_boundary", regexp_extract($"errorMessage", "\\bfor\\b|\\bin\\b", 0)).
    drop("errorMessage")

// Write the dataframe back to the dedicated SQL pool
df2.write.synapsesql("someDb.dbo.someTable_processed", Constants.INTERNAL)

// Debugging
//df2.show

你可以看到天真的检查显示“in”出现在你可能想要或不想要的“主要”中。我在这里使用单词边界RegEx标记\b来绕过它。

如果你觉得Spark SQL更好用，那么你也可以用它来创建数据帧：

val df2 = spark.sql("""
SELECT *,
    regexp_extract(errorMessage, "for|in", 0) for_or_in,
    regexp_extract(errorMessage, "\\bfor\\b|\\bin\\b", 0) for_or_in_with_word_boundary,

    locate(" for ", errorMessage) locate1,
    locate("in ", errorMessage) locate2
    
FROM errorMessages
""")

结果：

​

​

Answer 3

如果我没有使用Azure Synapse/SQL DW/SQL池(它的其他品牌名称也可能存在)，那么下面的代码示例可以作为替代

select  GREATEST  (patindex ('% for %', objectname),patindex ('% is %', objectname),patindex ('% my %', objectname)) from myTable