Separate letters from digits in alphanumeric string
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';
WITH R(Level,Input,Accumulator,StringLength)
AS (SELECT 0,
Input,
0,
DATALENGTH(Input)
FROM (SELECT REPLACE(@Input, '0', '')) D(Input)
UNION ALL
SELECT NewLevel,
NewInput,
Accumulator + NewLevel * ( StringLength - NewStringLength ),
NewStringLength
FROM R
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
FROM R
WHERE Level = 9;
Or you could use CLR and regular expressions (SQL Server 2012 compatible version).
using System;
using System.Data.SqlTypes;
using System.Collections;
using System.Text.RegularExpressions;
public partial class UserDefinedFunctions
{
private static readonly Regex digitRegex = new Regex(@"[\d]", RegexOptions.Compiled);
[Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName = "FillRow",
TableDefinition = @"Stripped NVARCHAR(MAX),
Total INT")]
public static IEnumerable ReplaceAndTotalise(SqlString input)
{
if (!input.IsNull)
{
int total = 0;
string stripped = digitRegex.Replace((string)input, match =>
{
total += int.Parse(match.Value);
return string.Empty;
});
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;
}
}
Example Usage
SELECT Stripped,
Total
FROM [dbo].[ReplaceAndTotalise]('GR35hc7vdH35')
Try the following:
CREATE FUNCTION dbo.AlphaNumericSplitter
(
@string varchar(8000)
)
RETURNS TABLE
AS
RETURN (
WITH Alphanumeric (col1)
AS (
-- Put out string into a cte table
SELECT @string
),
Nmbrs (n)
AS (
-- Numbers so we can split the string
SELECT TOP(LEN(@string))
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM sys.all_objects AS o1
CROSS
JOIN sys.all_objects AS o2
),
y
AS (
SELECT N.n
a.col1,
N.x,
-- Get the numbers only
Numbers = TRY_CONVERT(int, N.x)
FROM Alphanumeric AS a
CROSS
APPLY (SELECT [x] = SUBSTRING(a.col1, n, 1), Nmbrs.n FROM Nmbrs) AS N
)
SELECT z.Col1,
Col2 = SUM(y.Numbers)
FROM y
-- Get the letters only
CROSS
APPLY (SELECT (SELECT x + '' FROM y WHERE Numbers IS NULL ORDER BY y.n FOR XML PATH(''))) AS z (Col1)
GROUP BY
z.Col1);
GO
SELECT * FROM AlphaNumericSplitter('GR35hc7vdH35');
Results:
Kind of
... r.s ...
cross apply (
select s = sum(cast(substring(mycol,i,1) as int))
from (select top(len(mycol)) i = row_number() over(order by (select null))
from sys.all_objects,sys.all_objects) tally
where substring(mycol,i,1) like '[0-9]'
) r