1

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!

3

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.

  • Nice thinking about the reversal +1. – Vérace May 20 '15 at 2:35
3

You can convert your string to XML and use a cast as xs:int ? to check if the value is an integer or not.

declare @T table(C varchar(100));

insert into @T(C) values
('Widget (1234)'),
('Different Widget (123)'),
('Many Widgets (2x) (4567)');

select T.C,
       T2.X.value('(for $n in X return $n/text()[1] cast as xs:int ?)[1]', 'int') as Value
from @T as T
  cross apply (select cast(replace(replace(T.C, '(', '<X>'), ')', '</X>') as xml)) as T2(X)

Result:

C                          Value
-------------------------- -----------
Widget (1234)              1234
Different Widget (123)     123
Many Widgets (2x) (4567)   4567

The XML version of your third string (after the cast(replace)replace) looks like this:

Many Widgets <X>2x</X><X>4567</X>

The flwor in the value function walks through the X nodes and returns the first value that is an integer.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.