Dynamic extraction of multiple text from a single row - SQL

Question

The values in the table are in the following format

Returns Request for Order: 121212, RA Number:1654356, 4 Products:, 1 x "ABC" (11169595), Expected Action: Replace, 1 x "FGH" (11449119), Expected Action: Refund, 1 x "XCV" (11100873), Expected Action: Refund, 1 x "VVV" (11028561), Expected Action: Refund 
-------------------------------------------------------------------------------------------------------------------------------
Returns Request for Order: 234354, RA Number:3465646, 3 Products:, 3 x "FGH" (11449119), Expected Action: Replace 
-----------------------------------------------------------------------------------------------------
Returns Request for Order: 234355, RA Number:3465646, 3 Products:, 2 x "ABC" (11169595),  Expected Action: Replace, 1 x "FGH" (11449119), Expected Action: Refund

The following is the desired o/p format:

 Order_Num        REFUND_PRODUCTS                 REPLACE_PRODUCTS
 121212     11449119,11100873,11028561           11169595
 234354                                  11449119,11449119,11449119
 234355         11449119                       11169595,11169595

If its ' 2 x "ABC" (11169595) Expected Action: REPLACE' then under the REPLACE_PRODUCT column the number 11169595' should appear twice.

if its '3 x ....' then thrice. Similar for REFUND_PRODUCT

How can I go about this?

You have a challenge here for sure. Can you post create table statements and insert statements for the sample data? I am not sure if what you posted is a single row or multiple rows. — Sean Lange
– Sean Lange, Commented Feb 14, 2019 at 17:17
Yikes!!! Any chance you can fix the data structure? This is going to be rather ugly to unravel. — Sean Lange
– Sean Lange, Commented Feb 14, 2019 at 17:21
WOW this is truly awful. You are going to need a string splitter that maintains ordinal position. Here is the only set based one I know of that returns the position in the output. But that is barely scratching the surface of just how difficult this is going to be. — Sean Lange
– Sean Lange, Commented Feb 14, 2019 at 17:28

iainc · Accepted Answer · 2019-02-15 14:30:09Z

3

OK, as long as your format stays consistent you can use substrings and a modified version of the function found here. I've created a quick tally table here but you could swap this out if you already have a tally or numbers table to use.

As Sean mentioned in the comments, if this is a big table, this is going to take a while to run. I don't think there is really any way round that though as this is an exercise in chopping up strings.

Declare @mytable as table (mydata varchar(4000))

Declare @myresults as table (order_num int,  refund_products varchar(1000), replace_products varchar(1000))

declare @tally as table (t_idx int)
insert into @tally
select 1 UNION ALL
select 2 UNION ALL
select 3 UNION ALL
select 4 UNION ALL
select 5 UNION ALL
select 6 UNION ALL
select 7 UNION ALL
select 8 UNION ALL
select 9

INSERT INTO @mytable
Select 'Returns Request for Order: 121212 RA Number:1654356 4 Products: 1 x "ABC" (11169595) Expected Action: REPLACE 1 x "FGH" (11449119) Expected Action: REFUND 1 x "XCV" (11100873) Expected Action: REFUND 1 x "VVV" (11028561) Expected Action: REFUND' UNION ALL
Select 'Returns Request for Order: 234354 RA Number:3465646 3 Products: 3 x "FGH" (11449119) Expected Action: REPLACE' UNION ALL
Select 'Returns Request for Order: 234355 RA Number:3465646 3 Products: 2 x "ABC" (11169595)  Expected Action: REPLACE 1 x "FGH" (11449119) Expected Action: REFUND'

insert into @myresults
Select
substring(mydata,charindex('Order:',mydata, 0)+7,6),
substring(
        STUFF(
            (Select ',' + pos_string from dbo.FindPatternLocation(mydata,'(') FOR XML PATH('')),1,1,'' ) ,10,500),

STUFF(
        (Select ',' + substring(mydata ,charindex('(',mydata,0)+1,8) from @tally where t_idx<=substring(mydata,charindex('(',mydata,0)-10,1) FOR XML PATH('')),1,1,''
        )

from @mytable

Select * from @myresults

The function:

CREATE FUNCTION dbo.FindPatternLocation
(
    @string NVARCHAR(MAX),
    @term   NVARCHAR(255)
)
RETURNS TABLE
AS
    RETURN 
    (
      SELECT pos = Number - LEN(@term) ,

      substring(@string, Number - LEN(@term)+1, 8) as pos_string

      FROM (SELECT Number, Item = LTRIM(RTRIM(SUBSTRING(@string, Number, 
      CHARINDEX(@term, @string + @term, Number) - Number)))
      FROM (SELECT ROW_NUMBER() OVER (ORDER BY [object_id])
      FROM sys.all_objects) AS n(Number)
      WHERE Number > 1 AND Number <= CONVERT(INT, LEN(@string)+1)
      AND SUBSTRING(@term + @string, Number, LEN(@term)) = @term
    ) AS y);

As an alternative to the function you could use this as the second column:-

substring(
        STUFF(
            (Select ',' + pos_string from 

             (SELECT pos = Number - LEN('(') ,
              substring(mydata, Number - LEN('(')+1, 8) as pos_string

      FROM (SELECT Number, Item = LTRIM(RTRIM(SUBSTRING(mydata, Number, 
      CHARINDEX('(', mydata + '(', Number) - Number)))
      FROM (SELECT ROW_NUMBER() OVER (ORDER BY [object_id])
      FROM sys.all_objects) AS n(Number)
      WHERE Number > 1 AND Number <= CONVERT(INT, LEN(mydata)+1)
      AND SUBSTRING('(' + mydata, Number, LEN('(')) = '('
    ) aa) bb  FOR XML PATH('')),1,1,'' ) ,10,500)

edited Feb 15, 2019 at 14:30

answered Feb 14, 2019 at 18:02

iainc

8687 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Sean Lange Over a year ago

This is really close. The OP said that they want a comma delimited list in the replace column with the number repeated as many times as it says products. Another join to a tally table here with another stuff should get it. I sure hope the table the OP is using isn't very big.

iainc Over a year ago

Yep - I was in a hurry. Forgot to add the bit that used the count column to tally and multiply the replace column

iainc Over a year ago

Revised the script to deliver what the OP requested.

raven Over a year ago

thanks @iainc!. Is there a way to combine the function into the main code?

Sean Lange Over a year ago

Don't put code in the comments, it will not format at all. If you need to add some details to your answer you should edit your answer.

|

Collectives™ on Stack Overflow

Dynamic extraction of multiple text from a single row - SQL

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related