CODEWITHSUNDEEP
sqlsql-serversubstring
raven
raven

Reputation: 135

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

Upvotes: 1

Views: 58

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

If the product code is always the same length, then you can simply do:

select right(col, 8) as product_code

If the string may not look like this, then use case:

select (case when col like '% product %'  -- or whatever
             then right(col, 8)
        end) as product_code

If you want what comes after 'product':

select stuff(col, 1, charindex('product', col) + 7, '') as product_code

Upvotes: 0

Zahid Hassan Shaikot
Zahid Hassan Shaikot

Reputation: 1340

You can use SUBSTRING(string, start, length)

SELECT SUBSTRING(Your_value, 41) AS ExtractString;

Example 

SELECT SUBSTRING("Successfully refunded 1 units of product 11111111", 41) AS ExtractString;

Upvotes: 0

EzLo
EzLo

Reputation: 14189

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

Upvotes: 2

Related Questions