PATINDEX() where pattern is in list

Question

I need to create a substring of everything to the left of the second instance of a separator in a stack trace. The stack traces look like this:

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.

In this instance, "at" is the separator. However, these are user reports from all over the globe, so the "at" actually gets localized for their language. I can pull the possibilities easily by calling

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

Since the trace always starts with the localized "at"

What I would like to do is select something like

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

Is that possible, or do I need to do cases for each possible separator?

Answer 1

You can use a user defined function to locate the nth occurrence of a pattern like this.

    /*******************************************************************************
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

once you identify your separator, using this function you can search for the second occurence of your pattern (your separator) and select everything to the left of it. I used the sample you provided to test the function like this.

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)

You can read more on the user defined function here

EDIT: since you already figured out how to extract your separator, I hard coded the separator 'at ' just for testing. You should do something like this to handle the variation of your separator.

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

Answer 2

What I wound up doing was taking LEFT() of the first space character to define as a separator. Then I took a substring of everything to the right of the first space via substring(e.stacktrace, charindex(' ',e.stacktrace), len(e.stacktrace) - charindex(' ',e.stacktrace)+1)

From that, I did this:

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
    )

So this gave me everything to the left of the 2nd instance of ' [separator]' (the language-appropriate separator with two space characters before it), which is the first "section" of the stack trace (according to the definition of the stack trace I was given). It also handles cases where there is no second part to the stack trace.