For the set-based, tally table, CTE freaks among us, here is a set-based solution that uses a tally table and CTEs:

SET NOCOUNT ON;

-- The @Strings table variable represents whatever your source table is.

DECLARE @Strings TABLE ([StringKey] INT NOT NULL IDENTITY(1, 1),

[RawString] VARCHAR(1000) NOT NULL,

PRIMARY KEY CLUSTERED ([StringKey])

);

INSERT INTO @Strings ( [RawString] ) VALUES ( '' );

INSERT INTO @Strings ( [RawString] ) VALUES ( 'abc' );

INSERT INTO @Strings ( [RawString] ) VALUES ( '789' );

INSERT INTO @Strings ( [RawString] ) VALUES ( 'abc789' );

INSERT INTO @Strings ( [RawString] ) VALUES ( '789abc' );

INSERT INTO @Strings ( [RawString] ) VALUES ( 'abc789abc' );

INSERT INTO @Strings ( [RawString] ) VALUES ( '123rgt456tgb789asd' );

INSERT INTO @Strings ( [RawString] ) VALUES ( 'abc123rgt456tgb789' );

INSERT INTO @Strings ( [RawString] ) VALUES ( 'abc123rgt456tgb789asd' );

-- we create 6 Common Table Expressions (CTEs)

-- The first 3 are solely for the purpose of creating a tally table of 1,000 sequential numbers.

-- If you already have a tally table defined in your database you use it in the [Characters] CTE and remove the first 3 CTEs

-- The [Characters] CTE breaks each character of each string down into its own row with a value ([IsDigit]) indicating whether it is a numeric character or not

-- The [LastNumerics] CTE finds the location of the last numeric character in each string

-- The [[LastNonNumerics]] CTE finds the location of the last non-numeric character in each string that occurs BEFORE the last numeric character

WITH [First_10]([Dummy]) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1

), -- first 10 rows

[Full_1000]([Dummy]) AS (SELECT 1 FROM [First_10] a CROSS JOIN [First_10] b CROSS JOIN [First_10] c), -- 10 x 10 x 10 = 1000 rows

[Tally_1000]([SeqNo]) AS (SELECT ROW_NUMBER() OVER (ORDER BY [Dummy]) FROM [Full_1000]),

[Characters] AS (SELECT .[StringKey], [sn].[SeqNo], ISNUMERIC(SUBSTRING(.[RawString], [sn].[SeqNo], 1)) AS [IsDigit]

FROM @Strings s INNER JOIN [Tally_1000] AS sn ON [sn].[SeqNo] <= LEN(.[RawString])

WHERE [sn].[SeqNo] <= LEN(.[RawString])

),

[LastNumerics] AS (SELECT [c].[StringKey], MAX([c].[SeqNo]) AS [LastNumeric]

FROM [Characters] AS c

WHERE [c].[IsDigit] = 1

GROUP BY [c].[StringKey]

),

[LastNonNumerics] AS (SELECT [c].[StringKey], MAX([c].[SeqNo]) AS [LastNonNumeric]

FROM [Characters] AS c INNER JOIN [LastNumerics] n ON [n].[StringKey] = [c].[StringKey]

AND [c].[SeqNo] < [n].[LastNumeric]

WHERE [c].[IsDigit] = 0

GROUP BY [c].[StringKey]

)

-- Now we can do our SELECT query to extract the characters from each string starting right after the last non-numeric character and going through the last numeric character.

SELECT .[RawString], SUBSTRING(.[RawString], ISNULL([nn].[LastNonNumeric], -1) + 1, ISNULL([n].[LastNumeric], 0) - ISNULL([nn].[LastNonNumeric], -1)) AS [Result]

FROM @Strings s LEFT OUTER JOIN [LastNumerics] n ON [n].[StringKey] = .[StringKey]

LEFT OUTER JOIN [LastNonNumerics] nn ON [nn].[StringKey] = .[StringKey];