Selecting values between multiple parentheses in SQL Server 2014

Question

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?

very similar question here: http://stackoverflow.com/questions/42509024/get-string-between-2-characters-that-repeats-several-times-in-sql-server/42509706#42509706 — t-clausen.dk, Apr 04 '17 at 11:19

Serg · Accepted Answer · 2017-04-04T10:39:05.880

1

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;

edited Apr 04 '17 at 10:39

answered Apr 04 '17 at 10:28

Serg

22,285
5
21
48

Works perfectly! Thank you – attention Apr 04 '17 at 10:45
1

@attention You can thank someone in stackoverflow by upvoting and marking their answer as useful. – Jibin Balachandran Apr 04 '17 at 10:48
@serg Why `sys.all_objects a, sys.all_objects b` is needed? Can't we do the same with just one table?? – Jibin Balachandran Apr 04 '17 at 11:18
1

@JibinBalachandran OP has not posted the type of the `Code` column. If it's of `Varchar(max)` type we may need rather long series of numbers. – Serg Apr 04 '17 at 11:26

score 0 · Answer 2 · answered Apr 04 '17 at 10:28

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)

Selecting values between multiple parentheses in SQL Server 2014

2 Answers2