模式在列表中的PATINDEX()

Question

我需要创建堆栈跟踪中分隔符第二个实例左侧的所有内容的子字符串。堆栈跟踪如下所示：

at AutoTagCore.net.windward.autotag.controls.reports.ReportHandler.LaunchReport(IDocumentInfo props，RunReportParams props，RunReportVariables variables，ImportMetrics[]& metrics，BaseTag& gotoTag) at C:\src\Jenova\11.1\Merge\AutoTag\AutoTagCore\net\windward\autotag\controls\reports\ReportHandler.cs:line 155 at AutoTagCore.net.windward.autotag.controls.reports.ReportHandler.LaunchReport(IDocumentInfo docInfo，RunReportVariables props，RunReportVariables variables，BaseTag& gotoTag)等。

在这种情况下，"at“是分隔符。然而，这些是来自全球各地的用户报告，因此"at“实际上是针对他们的语言本地化的。我可以通过调用

select distinct left(e.[StackTrace], CHARINDEX(' ',e.[StackTrace]))

因为跟踪总是以本地化的"at“开始

我想做的是选择像这样的东西

select substring(e.stacktrace,charindex(' ',e.stacktrace), len(left(e.stacktrace, patindex('%' + IN LIST(select distinct left(e.[StackTrace], CHARINDEX(' ',e.[StackTrace]))) + ' %'    ,e.stacktrace)))) as trace

这是可能的吗，或者我需要为每个可能的分隔符做大小写？

Answer 1

您可以使用用户定义的函数来定位出现的第n个模式，如下所示。

    /*******************************************************************************
Description:    Find the Nth Occurrence of a Target string within another string.
                This function can search the string from either the left or the right -
                i.e. you can find the 3rd occurrence, the 2nd to last occurrence, etc.
                If an Nth occurrence is not found, returns zero.

Parameters:     - Input(s)
            @strTarget      - The string to search for
            @strSearched    - The string being searched
            @intOccurrence  - The specific occurrence to find:
                                Positive values search Left-to-Right, Negative values Right-to-Left

            - Output(s)
            Returns the character position of the @intOccurrence of @strTarget within @strSearched

Usage Example:
    SELECT dbo.udfCharIndex2('ow', 'how now brown cow', 3)
        returns the location of the third occurrence of 'ow' which is 11
    SELECT dbo.udfCharIndex2('ow', 'how now brown cow', -2)
        returns the location of the 2nd last occurrence of 'ow' which is 11
    SELECT dbo.udfCharIndex2('ow', 'how now brown cow', -1)
        returns the location of the last occurrence of 'ow' which is 16
    SELECT dbo.udfCharIndex2('ow', 'how now brown cow', 5)
        returns 0 since there are not 5 occurrences of 'ow'

*******************************************************************************/
CREATE FUNCTION dbo.udfCharIndex2(
    @strTarget varchar(8000), 
    @strSearched varchar(8000), 
    @intOccurrence smallint
    ) RETURNS smallint AS
BEGIN
    DECLARE @intPointer smallint, @intCounter smallint

    SELECT  @intCounter = 0,
            @intPointer = 0

    -- If Right2Left search, Reverse the Target & Searched strings
    IF @intOccurrence < 0
        SELECT @strTarget = Reverse(@strTarget),
            @strSearched = Reverse(@strSearched)

    WHILE (@intCounter < ABS(@intOccurrence))
    BEGIN
        SELECT  @intPointer = CharIndex(@strTarget, @strSearched, @intPointer + 1),
                @intCounter = @intCounter + 1

        -- If Target not found, exit loop
        IF @intPointer = 0 SET @intCounter = ABS(@intOccurrence)
    END

    -- If Right2Left search, map Pointer from reversed strings back to forward strings
    IF @intOccurrence < 0 AND @intPointer <> 0 SET @intPointer = DataLength(@strSearched) - @intPointer - DataLength(@strTarget) + 2

    RETURN(@intPointer)
END

一旦你确定了你的分隔符，使用这个函数，你可以搜索你的模式的第二次出现(你的分隔符)，并选择它左边的所有东西。我使用您提供的示例来测试函数，如下所示。

DECLARE @str VARCHAR(900)
SET @str = 'at AutoTagCore.net.windward.autotag.controls.reports.ReportHandler.LaunchReport(IDocumentInfo docInfo, RunReportParams props, RunReportVariables variables, ImportMetrics[]& metrics, BaseTag& gotoTag) in C:\src\Jenova\11.1\Merge\AutoTag\AutoTagCore\net\windward\autotag\controls\reports\ReportHandler.cs:line 155 at AutoTagCore.net.windward.autotag.controls.reports.ReportHandler.LaunchReport(IDocumentInfo docInfo, RunReportParams props, RunReportVariables variables, BaseTag& gotoTag) in etc. etc.'
SELECT LEFT(@str,DBO.UDFCHARINDEX2('at ', @str, 2)-1)

您可以阅读有关用户定义函数here的更多信息

编辑：因为你已经知道了如何提取分隔符，所以我硬编码了分隔符'at‘，只是为了测试。您应该这样做，以处理分隔符的变化。

SELECT LEFT(@str,(DBO.UDFCHARINDEX2(left(@str, CHARINDEX(' ', @str)), @str, 2)-1))

Answer 2

我最后做的是将第一个空格字符的LEFT()定义为分隔符。然后，我通过substring(e.stacktrace, charindex(' ',e.stacktrace), len(e.stacktrace) - charindex(' ',e.stacktrace)+1)获取第一个空格右侧的所有内容的子字符串

从那开始，我这样做了：

left(
    substring(e.stacktrace, charindex(' ',e.stacktrace), len(e.stacktrace) - charindex(' ',e.stacktrace)+1),
    CASE 
    WHEN patindex('%  ' + left(e.[StackTrace], CHARINDEX(' ',e.[StackTrace])) + '%', substring(e.stacktrace, charindex(' ',e.stacktrace), len(e.stacktrace) - charindex(' ',e.stacktrace)+1)) > 0
    THEN patindex('%  ' + left(e.[StackTrace], CHARINDEX(' ',e.[StackTrace])) + '%', substring(e.stacktrace, charindex(' ',e.stacktrace), len(e.stacktrace) - charindex(' ',e.stacktrace)+1))
    ELSE len(substring(e.stacktrace, charindex(' ',e.stacktrace), len(e.stacktrace) - charindex(' ',e.stacktrace)+1))
    END
    )

这就给了我第二个“分隔符”(前面有两个空格字符的语言合适的分隔符)实例左边的所有东西，它是堆栈跟踪的第一个“部分”(根据给出的堆栈跟踪的定义)。它还处理堆栈跟踪没有第二部分的情况。