Split String and Summarise Values into Columns

Question

I have a table full of strings that look like the following: P12D1P13D1P2D47R1D37P3D1R10D10

What would be the best way to split the string by P, D and R and then SUM per each letter group?

For example summarised as columns:

P	D	R
30	97	11

Answer 1

Here is an option using CROSS APPLY and string_split() and a conditional aggregation

Example

Declare @YourTable Table (id int,[SomeCol] varchar(50))  Insert Into @YourTable Values 
 (1,'P12D1P13D1P2D47R1D37P3D1R10D10')
 
Select A.id
      ,C.*
 From  @YourTable A
 Cross Apply ( values (replace(replace(replace(SomeCol,'P','|P'),'D','|D'),'R','|R'))) B(S)
 Cross Apply ( Select D = sum(case when Item='D' then Val end)
                     ,P = sum(case when Item='P' then Val end)
                     ,R = sum(case when Item='R' then Val end)
                From  string_split(S,'|')  C1
                Cross Apply (values (left(value,1),try_convert(int,substring(value,2,25)))) C2(Item,Val)
             ) C

Results

id  D   P   R
1   97  30  11

Answer 2

You can use REPLACE to add a , before each string token. Then use STRING_SPLIT to split them up, and SUBSTRING to get the number part (which you can convert using TRY_CAST or TRY_CONVERT).

Put all that into a subquery and use conditional SUM

SELECT
  t.*,
  s.*
FROM YourTable t
CROSS APPLY (
    SELECT
      SUM(TRY_CAST(CASE WHEN s.value LIKE 'P%' THEN SUBSTRING(s.value, 2, LEN(s.value) - 1) END AS int)) AS P,
      SUM(TRY_CAST(CASE WHEN s.value LIKE 'D%' THEN SUBSTRING(s.value, 2, LEN(s.value) - 1) END AS int)) AS D,
      SUM(TRY_CAST(CASE WHEN s.value LIKE 'R%' THEN SUBSTRING(s.value, 2, LEN(s.value) - 1) END AS int)) AS R
    FROM STRING_SPLIT(
      REPLACE(
        REPLACE(
          REPLACE(
            t.BadData,
            'P', ',P'
          ),
          'D', ',D'
        ),
        'R', ',R'
      ), ',') s
      WHERE s.value <> ''
) s

db<>fiddle