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
Advertisement
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