SQL Server – How to Extract Number Code from Strings in Parentheses

sql-server-2008substringt-sql

I have string data (ProductDescription) that includes product names and ID numbers and need to pull the ID numbers for cross-referencing…

Data Samples:

Widget (1234)
Different Widget (123)
Many Widgets (2x) (4567)

I am trying to do this with a rather messy CASE statement:

CASE
    WHEN
        ISNUMERIC(
        SUBSTRING(
            ProductDescription
            ,CHARINDEX('(',ProductDescription)+1
            ,CASE 
                WHEN
                    CHARINDEX(')',ProductDescription)
                        -CHARINDEX('(',ProductDescription)-1 > 0
                    THEN CHARINDEX(')',ProductDescription)
                        -CHARINDEX('(',ProductDescription)-1
                ELSE 0 END
            )) = 1
    THEN
        SUBSTRING(
            ProductDescription
            ,CHARINDEX('(',ProductDescription)+1
            ,CASE 
                WHEN 
                    CHARINDEX(')',ProductDescription)
                        -CHARINDEX('(',ProductDescription)-1 > 0
                    THEN CHARINDEX(')',ProductDescription)
                        -CHARINDEX('(',ProductDescription)-1
                ELSE 0 END
            )
    ELSE 0 END

In cases where there is a parenthetic insertion in the product name (as in the "Many Widgets (2x)" example), I end up returning the "2x" value, which is not what I want.

How can I look for only numeric values contained in parentheses and discard the contents of others? Is there an easier/cleaner way to do the simple CHARINDEX work I'm currently attempting?

Thanks!

Best Answer

If the data you need is always at the end of the string, would something like this work?

declare @test varchar(200);

set @test= 'Many Widgets (2x) (4567)';

select substring(@test,
                 (len(@test) - charindex('(',reverse(@test))) + 2,
                 charindex('(',reverse(@test)) - 2
                );

We can reverse the string, find the first open paren (which is really the last open paren), and go from there.

Related Question