CODEWITHSUNDEEP
sqlsql-server
attention
attention

Reputation: 23

Selecting values between multiple parentheses in SQL Server 2014

There is a table that holds long code string. I do need to select only short codes in between multiple parentheses inside the string.

I was using this:

SELECT 
    Code,
    SUBSTRING(Code,CHARINDEX('(',Code)+1,(CHARINDEX(')',Code)-CHARINDEX('(',Code))-1) AS CodeSplit
FROM
    #temp1

It only returns the value in the first parentheses, but I do need it to loop and return codes from all of the parentheses within the string.

Any ideas how to do this?

enter image description here

Upvotes: 0

Views: 619

Answers (2)

Serg
Serg

Reputation: 22811

You need tally table of numbers. Demo

SELECT 
    Code,
    SUBSTRING(Code,s+1,CHARINDEX(')',Code,s)-s-1) AS CodeSplit
FROM
    (   -- test data
        SELECT '(123)yui(rty)999(cvb)' Code
    ) t
    CROSS APPLY (
        SELECT DISTINCT CHARINDEX('(',Code, n) s
        FROM (
         -- use any tally at hand
         SELECT TOP(len(Code)) row_number() over(order by (select null)) n
         FROM sys.all_objects a, sys.all_objects b
        ) tally
    ) strt
WHERE s>0;

If you need concatenation in a single row

SELECT 
     Code,
     (SELECT 
         SUBSTRING(Code,s+1,CHARINDEX(')',Code,s) - s - 1) 
      FROM ( 
          SELECT DISTINCT CHARINDEX('(',Code, n) s
          FROM (
             SELECT TOP(len(Code)) row_number() over(order by (select null)) n
             FROM sys.all_objects a, sys.all_objects b
             )  tally 
          ) strt
      WHERE s>0 
      FOR XML PATH(''))
FROM
    (   -- test data
        SELECT '(123)yui(rty)999(cvb)' Code
    ) t;

Upvotes: 1

TriV
TriV

Reputation: 5148

You could try it

DECLARE @SampleDate AS TABLE (Code varchar(max))

INSERT INTO @SampleDate VALUES ('[1](2) [44]  (2432) fff '), ('[1](2)(33)')

;WITH temps AS 
(
   SELECT sd.Code AS RootCode, Substring(sd.Code, CHARINDEX(')',Code) + 1, len(sd.Code) - CHARINDEX(')',Code)) AS CurrentCode,  
                         SUBSTRING(Code,CHARINDEX('(',Code)+1,(CHARINDEX(')',Code)-CHARINDEX('(',Code))-1) AS CurrentCodeSplit 
   FROM @SampleDate sd

   UNION ALL

   SELECT t.RootCode , Substring(t.CurrentCode, CHARINDEX(')',t.CurrentCode) + 1, len(t.CurrentCode) - CHARINDEX(')',t.CurrentCode)), 
                t.CurrentCodeSplit + ', ' + SUBSTRING(t.CurrentCode,CHARINDEX('(',t.CurrentCode)+1,(CHARINDEX(')',t.CurrentCode)-CHARINDEX('(',t.CurrentCode))-1) 
   FROM temps t
   WHERE CHARINDEX('(',t.CurrentCode) > 0
)
SELECT t.RootCode AS Code, max(t.CurrentCodeSplit) AS CodeSplit FROM temps t
GROUP BY t.RootCode
OPTION (MAXRECURSION 0)

Upvotes: 0

Related Questions