Parse large string into multiple columns/rows

Question

I have a string column that I need to parse into multiple different columns and rows.

Sample string value:

If the total charge exceeds {$10,000.00} ,pricing is recalculated to be a {50.00}% discount off charges, not to exceed an average daily charge of {$1,000.00}{2,3}

Desired output:

Price      Price_Type    Sequence
=====      ==========    ========
10000.00   Dollar        1
50.00      Percent       2
1000.00    Dollar        3

A few things to note:

1. I'm only looking to parse Price values that have either '$' or '%' attached to it. '$' is within the brackets before the Price value, '%' is outside the brackets and after the Price value
2. It's possible for Price values to not be encapsulated in brackets, but most are
3. There are values encapsulated in brackets that I don't want to capture
4. Sequence matters. The price value parsed from the left-most side of the string is 1 and so on
5. There are 100,000+ distinct values for the string with significant variation
6. I'm using SQL Server 2017

I'm trying to avoid tedious 100+ lines of CASE statements. I'm guessing the solution will involve a recursive function, recursive CTE, table-valued function, or some combination of these. My attempts at this have not gotten far.

There are essentially two looping mechanisms that need to occur: one to get all rows for a single string and another to go through all the distinct strings.

Answer 1

You could try some XML-trickery.

declare @S varchar(max) = 'If the total charge exceeds {$10,000.00} ,pricing is recalculated to be a {50.00}% discount off charges, not to exceed an average daily charge of {$1,000.00}{2,3}';
declare @X xml = replace(replace(replace((select @S for xml path('')), '{', '<X>'), '}%', '%</X>'), '}', '</X>');

with C as
(
  select T.X.value('.', 'varchar(50)') as V,
         row_number() over(order by T.X) as Sequence
  from @X.nodes('/X/text()') as T(X)
)
select replace(replace(C.V, '%', ''), '$', '') as Price,
       case 
         when right(C.V, 1) = '%' then 'Percent'
         when left(C.V, 1) = '$' then 'Dollar'
       end as Price_Type,
       C.Sequence
from C
where left(C.V, 1) = '$' or
      right(C.V, 1) = '%';

The code first creates a XML capturing the values in a node <X> by replacing the the brackets with start tag/end tag. Note that there is a special replace for making the % sign end up as part of element X.

If the total charge exceeds <X>$10,000.00</X> ,pricing is recalculated to be a <X>50.00%</X> discount off charges, not to exceed an average daily charge of <X>$1,000.00</X>)<X>2,3</X>)

Then it queries the XML to get the nodes and look at % and $ to figure out what is what.