如果您的第三个数字是字符串中的最后一个数字（总是），您可以使用下面的逻辑来获取第三个数字-
此处演示

DECLARE @T VARCHAR(1000)
SET @T = 'Successfully imported 6609 records and updated 0 records for a total of 6609 records into the table'

SELECT 
REVERSE
(
    SUBSTRING
    (
        REVERSE(@T),
        PATINDEX('%[0-9]%', REVERSE(@T)),
        (CHARINDEX(' ',REVERSE(@T),PATINDEX('%[0-9]%', REVERSE(@T))))-(PATINDEX('%[0-9]%', REVERSE(@T)))
    )
)

也许不是最好的解决方案，但它是有效的：

WITH source(message) AS
  (SELECT 'Successfully imported 1 records and updated 2 records for a total of 3 records into the table')
SELECT value
FROM SOURCE CROSS apply
  (SELECT row_number() OVER (ORDER BY message) POSITION, value
   FROM string_split(message, ' ')
   WHERE try_cast(value AS int) IS NOT NULL) AS a
WHERE POSITION = 3

一般来说，数据库不适合进行字符串操作，这种工作应该在前端进行。但是，您可以使用以下方法

SELECT TOP 1 WITH TIES T.String, SS.Value
FROM
(
  VALUES
  ('Successfully imported 6609 records and updated 0 records for a total of 6609 records into the table'),
  ('Successfully imported 6609 records and updated 0 records for a total of 9999 records into the table')
) T(String)
CROSS APPLY
(
  SELECT ROW_NUMBER() OVER(ORDER BY TRY_CAST(Value AS INT)) RN,
         Value
  FROM STRING_SPLIT(String, ' ')
) SS
ORDER BY RN DESC;
``` `Online Demo` 

我已经尝试了下面的查询，它的工作如预期。

SELECT SUBSTRING
('Successfully imported 0 records and updated 0 records for a total of 0 records into the table .%' ,
PATINDEX('%total of%' ,'Successfully imported 0 records and updated 0 records for a total of 0 records into the table .%') + 9, 
PATINDEX('%records into the table%' ,'Successfully imported 0 records and updated 0 records for a total of 0 records into the table .%') -
(PATINDEX('%total of%' ,'Successfully imported 0 records and updated 0 records for a total of 0 records into the table .%') + 9) )

基本上在你的两个例子，你想要的价值后 total of ，找到它，然后找到后面的第一个空格：

SELECT TRY_CONVERT(int,SUBSTRING(V.YourString,PI.I+DATALENGTH('total of '),CHARINDEX(' ',V.YourString,PI.I+DATALENGTH('total of ')) - (PI.I+DATALENGTH('total of '))))
FROM (VALUES('Successfully imported 6609 records and updated 0 records for a total of 6609 records into the table'),
            ('Successfully imported 6 records and updated 0 records for a total of 6 records into the table .'))V(YourString)
     CROSS APPLY (VALUES(PATINDEX('%total of%',V.YourString)))PI(I)