Sql Extract Between Parentheses

have a table(Item) with a field (ItemName)that I need to extract every thing between parentheses and put into an alias field.  I.e.

ItemName                                        Alias
part123 (12-1) (23-1)                       12-1, 23-1
part456 (45) (45-2345) (12)                45, 45-2345, 12
idealaeroAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mike EghtebasDatabase and Application DeveloperCommented:
Declare @v varchar(20)='part123 (12-1) (23-1)';
Select Replace(Replace(substring(@v,charindex('(',@v)+1,1000),')',''),'(','') As AliasName
Scott PletcherSenior DBACommented:
If the values will always be contiguous, this is easy:

SELECT ItemName,
    REPLACE(REPLACE(SUBSTRING(ItemName, open_paren_byte, close_paren_byte - open_paren_byte + 1), '(', ''), ')', ',')
FROM (
    SELECT 'part123 (12-1) (23-1)' as itemname union all
    select 'part456 (45) (45-2345) (12)' union all
    select 'part789 (56) (56-5678) more text'
) AS test_data
cross apply (
    SELECT CHARINDEX('(', itemname) AS open_paren_byte,
        LEN(itemname) - CHARINDEX(')', REVERSE(itemname)) AS close_paren_byte
) as assign_alias_names
idealaeroAuthor Commented:
I should clarify…

I have a table Named (Item)
In table (Item)  I have a column Named (ItemName)
This table has 89000 + records.

In the ItemName column  there maybe one alias name contained in parentheses there maybe 10 they may be separated by a comma, space or not at all.

Essentially I need to extract everything contained inside an open and closed parentheses and put into a field called alias.
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Mike EghtebasDatabase and Application DeveloperCommented:
You can apply it like:
Select 
Replace(Replace(substring(ItemName ,charindex('(',ItemName )+1,1000),')',''),'(','') As AliasName 
From Item

Open in new window

idealaeroAuthor Commented:
Eghtebas,

Very close.

The script is returning the entire ItemName field if it doesn't find a parentheses where it should return Null.  I can handle this with a case statement, but I think there are more efficient ways. also if there is text after the parentheses it is displayed where it shouldn't.
Mike EghtebasDatabase and Application DeveloperCommented:
Is there a way to list all variations possible for ItemName so we can code accordingly?
idealaeroAuthor Commented:
Unfortunately there isn't

The data I'm work with is 20 + years old with no controls placed on it.

The best I can come up with is everything between an open and close parentheses and nothing else.
The project I'm working is implementing a new system and I don't want to drag garbage data into the new system.
Mike EghtebasDatabase and Application DeveloperCommented:
We need ScottPletcher's attention to handle this.

It probably is best to use in inline function considering you will not use this routinely but for data migration.

Mike
Scott PletcherSenior DBACommented:
My code above gets everything from the first ( to the last ), ignoring any other leading or trailing data.  I show sample data and the results above.

If you have "in-between" data, such as "abc (123) def (456)" and you want to get "123, 456" then that will take additional code.
idealaeroAuthor Commented:
What I have is "abc (123) def (456) ghi" and I need "123, 456"

This is exactly what I have

Here is the code I have developed so far with Mike's help.

Select
Case
When Replace(Replace(substring(IMA_ItemName ,charindex('(',IMA_ItemName )+1,1000),')',''),'(',' ,') = IMA_ItemName then Null
Else
Replace(Replace(substring(IMA_ItemName ,charindex('(',IMA_ItemName )+1,1000),')',''),'(','') end As AliasName

FROM         Item
idealaeroAuthor Commented:
I took this query and gave it to the owner of the data and they are cleaning it up manually.  The farther I dug into the data the dirtier it became.
Scott PletcherSenior DBACommented:
Wow, I wish I had known you were abandoning this, I wouldn't have spent time coding up a solution that worked for "abc (123) def (456) ghi (789) jkl ...".
Mike EghtebasDatabase and Application DeveloperCommented:
Hi idealaero,

Because the solution from Scott is more accurate and much closer to what originally you were asking for, his solution ought to get recognition and the points. I understand there was a change of plan in the office for that you may give me 100 point for assist.
 
Because EE makes a repository of good questions for the future readers, it is important the accepted answer match the original question.

Thank you,

Mike
Mike EghtebasDatabase and Application DeveloperCommented:
Hi idealaero,

I forgot the last part. Please put a request via "Request Attention" button below your original question asking this question to be opened to be regraded.

Thanks,

Mike
Scott PletcherSenior DBACommented:
Here's code that uses a "standard" tally table -- a table of sequential numbers -- to pull out all values enclosed in ().  [I tried to post code to create a tally table but my server at worked blocked it -- I'll try again in a bit.]

SELECT IMA_ItemName, STUFF(CAST((
    SELECT ', ' + SUBSTRING(IMA_ItemName, tally + 1, CHARINDEX(')',  IMA_ItemName, tally + 1) - tally - 1)
    FROM dbo.tally tally
    WHERE SUBSTRING(IMA_ItemName, tally, 1) = '('
    FOR XML PATH('')
    ) AS varchar(8000)), 1, 2, '')
FROM ( --dbo.your_table_name_here
    SELECT 'part123 (12-1) (23-1) abc' as IMA_ItemName union all
    select 'part456 (45) ab(45-2345)xq zprl(12)' union all
    select 'abc (123) def (456) ghi' union all
    select 'part789'
) AS test_data_as_an_example
Scott PletcherSenior DBACommented:
CREATE TABLE dbo.tally (
    tally int NOT NULL,
    CONSTRAINT tally__PK PRIMARY KEY CLUSTERED ( tally )
    )
INSERT INTO dbo.tally VALUES(0);

WITH
cteTally10 AS (
    SELECT 0 AS tally UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL
    SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0
),
cteTally100 AS (
    SELECT 1 AS tally
    FROM cteTally10 c1
    CROSS JOIN cteTally10 c2
),
cteTally10K AS (
    SELECT 1 AS tally
    FROM cteTally100 c1
    CROSS JOIN cteTally100 c2
),
cteTally1Mil AS (
    SELECT ROW_NUMBER() OVER(ORDER BY c1.tally) AS tally
    FROM cteTally100 c1
    CROSS JOIN cteTally10K c2
)
INSERT INTO dbo.tally
SELECT tally
FROM cteTally1Mil
ORDER BY tally

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.