Got it, let's fix this problem of reliably pulling the 3rd numeric value from your message—no more hardcoding positions that break when text or number lengths change. Your current approach works for specific strings but fails when numbers are shorter/longer or the message structure shifts, so we need a dynamic way to target the 3rd set of digits.

Solution 1: Nested PATINDEX (Compatible with Most SQL Versions) #

This method manually skips the first two numeric values by finding their start/end positions, then extracts the third one dynamically. It handles numbers of any length and works even if the surrounding text varies.

DECLARE @message NVARCHAR(MAX) = 'Successfully imported 6609 records and updated 0 records for a total of 6609 records into the table';

-- Step 1: Skip the first numeric value
DECLARE @firstNumStart INT = PATINDEX('%[0-9]%', @message);
DECLARE @firstNumEnd INT = @firstNumStart + PATINDEX('%[^0-9]%', SUBSTRING(@message, @firstNumStart, LEN(@message))) - 2;
DECLARE @afterFirstNum NVARCHAR(MAX) = SUBSTRING(@message, @firstNumEnd + 2, LEN(@message));

-- Step 2: Skip the second numeric value
DECLARE @secondNumStart INT = PATINDEX('%[0-9]%', @afterFirstNum);
DECLARE @secondNumEnd INT = @secondNumStart + PATINDEX('%[^0-9]%', SUBSTRING(@afterFirstNum, @secondNumStart, LEN(@afterFirstNum))) - 2;
DECLARE @afterSecondNum NVARCHAR(MAX) = SUBSTRING(@afterFirstNum, @secondNumEnd + 2, LEN(@afterFirstNum));

-- Step 3: Extract the third numeric value
DECLARE @thirdNumStart INT = PATINDEX('%[0-9]%', @afterSecondNum);
DECLARE @thirdNumEnd INT = @thirdNumStart + PATINDEX('%[^0-9]%', SUBSTRING(@afterSecondNum, @thirdNumStart, LEN(@afterSecondNum))) - 1;
-- Handle edge case where third number is at the end of the string
IF @thirdNumEnd = 0 SET @thirdNumEnd = LEN(@afterSecondNum);

DECLARE @thirdNum NVARCHAR(MAX) = SUBSTRING(@afterSecondNum, @thirdNumStart, @thirdNumEnd - @thirdNumStart + 1);

SELECT @thirdNum AS ThirdNumericValue;

Test with Your Shorter Message #

If you update @message to:

SET @message = 'Successfully imported 6 records and updated 0 records for a total of 6 records into the table .';

The query will return 6 as expected.

Solution 2: Recursive CTE (Flexible for Any Nth Number) #

If you ever need to extract the Nth numeric value (not just the 3rd), this recursive CTE approach is more scalable. Just adjust the @targetIndex variable to pick the number you want.

DECLARE @message NVARCHAR(MAX) = 'Successfully imported 6609 records and updated 0 records for a total of 6609 records into the table';
DECLARE @targetIndex INT = 3; -- Target the 3rd numeric value

WITH NumericValues AS (
    SELECT
        PATINDEX('%[0-9]%', @message) AS NumStart,
        PATINDEX('%[^0-9]%', SUBSTRING(@message, PATINDEX('%[0-9]%', @message), LEN(@message))) AS NumLength,
        1 AS IndexNum,
        SUBSTRING(@message, PATINDEX('%[0-9]%', @message) + PATINDEX('%[^0-9]%', SUBSTRING(@message, PATINDEX('%[0-9]%', @message), LEN(@message))), LEN(@message)) AS RemainingText
    UNION ALL
    SELECT
        PATINDEX('%[0-9]%', RemainingText) AS NumStart,
        PATINDEX('%[^0-9]%', SUBSTRING(RemainingText, PATINDEX('%[0-9]%', RemainingText), LEN(RemainingText))) AS NumLength,
        IndexNum + 1 AS IndexNum,
        SUBSTRING(RemainingText, PATINDEX('%[0-9]%', RemainingText) + PATINDEX('%[^0-9]%', SUBSTRING(RemainingText, PATINDEX('%[0-9]%', RemainingText), LEN(RemainingText))), LEN(RemainingText)) AS RemainingText
    FROM NumericValues
    WHERE PATINDEX('%[0-9]%', RemainingText) > 0
)
SELECT
    SUBSTRING(
        CASE IndexNum
            WHEN 1 THEN @message
            ELSE (SELECT RemainingText FROM NumericValues WHERE IndexNum = @targetIndex - 1)
        END,
        NumStart,
        CASE WHEN NumLength = 0 THEN LEN(CASE IndexNum WHEN 1 THEN @message ELSE (SELECT RemainingText FROM NumericValues WHERE IndexNum = @targetIndex - 1) END) ELSE NumLength - 1 END
    ) AS ThirdNumericValue
FROM NumericValues
WHERE IndexNum = @targetIndex;

Why Your Original Query Failed #

Your initial approach used hardcoded values like +9 and length 5, which only work if the text "total of" is exactly in that position and the number is 5 digits long. These dynamic methods avoid that by calculating each number's position on the fly, so they adapt to any number length or text variation.

内容的提问来源于stack exchange，提问作者Neha Singh