1

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.

1
  • 3
    This is something you probably don't want to do in pure SQL. It'll be painful, inefficient, and probably won't be code complete. CLR is probably a better solution if this must be called / executed from the database. Otherwise, yea a recursive function will probably be what you need, but scalar functions are parallelism inhibitors, and all around poor performing.
    – J.D.
    Commented Jan 25, 2023 at 21:01

1 Answer 1

2

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.

3
  • I've tested your solution on a number of the string variations and it works well enough, however it doesn't solve the need for repeating it through 100k string values. I made some tweaks to your code and wrapped it in a while loop to test out on small portions of the string. I will steadily increase the number of loops until performance becomes an issue then decide how to proceed. The nice thing about your solution is it's quickly pointing me towards anomalies in the data that I'm slowly fixing. I'll post my solution once it's closer to completion.
    – Doc
    Commented Jan 27, 2023 at 18:38
  • @Doc There is no need for a loop. Create a temporary table with an xml column. Fill the temp table with the generated XML and then query the table. The CTE has to of course be modified to query the temp table and you have to put .nodes(... shredding in a cross apply. Commented Jan 30, 2023 at 7:41
  • I know that using loops in SQL is generally considered a no-no, however I worked my way up to running it on all 100k values. Ran in about 15 minutes and gave me the result I was looking for. I won't be needing to generate this data often so I'm going to move on, but I may come back and try out your suggestion later when I'm not pressed for time. I have virtually no experience working with XML so it's a bit of a blind spot for me.
    – Doc
    Commented Jan 30, 2023 at 17:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.