Dynamic Extraction of text – SQL

I have a column with values in the following format:

Successfully refunded 1 units of product 11111111
Successfully refunded 1 units of product 22222222 Successfully refunded 1 units of product 33333333 Successfully refunded 1 units of product 55555555 
Successfully refunded 1 units of product 44444444
Successfully refunded 1 units of product 67676767 
Successfully refunded 1 units of product 90909090 Successfully refunded 1 units of product 36456421

how can i extract the number after ‘product’. It is simple to extract when there is one ‘product’.

Expected Result:

11111111
22222222,33333333,55555555,44444444,
67676767
90909090,36456421

Answer

You can get away with a few REPLACE:

DECLARE @Table TABLE (TextValues VARCHAR(MAX))

INSERT INTO @Table (TextValues)
VALUES
    ('Successfully refunded 1 units of product 11111111'),
    ('Successfully refunded 1 units of product 22222222 Successfully refunded 1 units of product 33333333 Successfully refunded 1 units of product 55555555 Successfully refunded 1 units of product 44444444'),
    ('Successfully refunded 1 units of product 67676767'),
    ('Successfully refunded 1 units of product 90909090 Successfully refunded 1 units of product 36456421')


SELECT
    Replaced = STUFF(
        REPLACE(
            REPLACE(T.TextValues, 'Successfully refunded 1 units of product ', ','),
            ' ', ''),
        1, 2, '')
FROM
    @Table AS T

Results:

Replaced
1111111
2222222,33333333,55555555,44444444
7676767
0909090,36456421