SQL Server doesn't support replacing patterns of multiple characters - so to do it via
REPLACE would take potentially 10 operations.
With that in mind one way of doing it would be a recursive CTE to process the digits 0-9 sequentially.
It does the replacement and then checks the length of the before and after strings to know how many characters of that number there were and what needs to be added on to the total.
DECLARE @Input VARCHAR(8000) = 'GR35hc7vdH35';
AS (SELECT 0,
FROM (SELECT REPLACE(@Input, '0', '')) D(Input)
Accumulator + NewLevel * ( StringLength - NewStringLength ),
CROSS APPLY (SELECT Level + 1) C(NewLevel)
CROSS APPLY (SELECT REPLACE(Input, NewLevel, '')) C2(NewInput)
CROSS APPLY (SELECT DATALENGTH(NewInput)) C3(NewStringLength)
WHERE NewLevel <= 9)
SELECT Input AS Col1,
Accumulator AS Col2
WHERE Level = 9;
Or you could use CLR and regular expressions (SQL Server 2012 compatible version).
public partial class UserDefinedFunctions
private static readonly Regex digitRegex = new Regex(@"[\d]", RegexOptions.Compiled);
[Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName = "FillRow",
TableDefinition = @"Stripped NVARCHAR(MAX),
public static IEnumerable ReplaceAndTotalise(SqlString input)
int total = 0;
string stripped = digitRegex.Replace((string)input, match =>
total += int.Parse(match.Value);
yield return new Tuple<string, int>(stripped, total);
public static void FillRow(object resultObject, out SqlString stripped, out SqlInt32 total)
var result = (Tuple<string, int>)resultObject;
stripped = result.Item1;
total = result.Item2;