Extract each string between each set of two different strings

Question

If I have for example the string:

'x(abc),y(def),z(ghi)'

How can I extract out either a comma delimited string:

'abc,def,ghi'

or a table of the strings:

'abc'
'def'
'ghi'

that exist between the two strings '(' and ')'?

They are comma delimited, but each 'element' can be of variable length.

Are you storing multiple values in a cell? – IShubh Jan 10 '19 at 17:22 — IShubh, Jan 10 '19 at 17:22

score 2 · Accepted Answer · answered Jan 10 '19 at 17:37

If open to a TVF. Being a TVF, it is easy to use in a CROSS APPLY if your data is in a table.

Example

Declare @S varchar(max) = 'x(abc),y(def),z(ghi)'

Select * from [dbo].[tvf-Str-Extract](@S,'(',')')

Returns

RetSeq  RetPos  RetVal
1       3       abc
2       10      def
3       17      ghi

The Function if Interested

CREATE FUNCTION [dbo].[tvf-Str-Extract] (@String varchar(max),@Delimiter1 varchar(100),@Delimiter2 varchar(100))
Returns Table 
As
Return (  

with   cte1(N)   as (Select 1 From (values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N(N)),
       cte2(N)   as (Select Top (IsNull(DataLength(@String),0)) Row_Number() over (Order By (Select NULL)) From (Select N=1 From cte1 N1,cte1 N2,cte1 N3,cte1 N4,cte1 N5,cte1 N6) A ),
       cte3(N)   as (Select 1 Union All Select t.N+DataLength(@Delimiter1) From cte2 t Where Substring(@String,t.N,DataLength(@Delimiter1)) = @Delimiter1),
       cte4(N,L) as (Select S.N,IsNull(NullIf(CharIndex(@Delimiter1,@String,s.N),0)-S.N,8000) From cte3 S)

Select RetSeq = Row_Number() over (Order By N)
      ,RetPos = N
      ,RetVal = left(RetVal,charindex(@Delimiter2,RetVal)-1) 
 From  ( Select *,RetVal = Substring(@String, N, L) From cte4 ) A
 Where charindex(@Delimiter2,RetVal)>1
)
/*
Max Length of String 1MM characters

Declare @String varchar(max) = 'Dear [[FirstName]] [[LastName]], ...'
Select * From [dbo].[tvf-Str-Extract] (@String,'[[',']]')
*/

You need to put that on Git with a good readme file explaining it lol — S3S, Jan 10 '19 at 17:44
Well I don't understand it but it works as a black box! What's the Max Length? 1MM characters? — TDP, Jan 11 '19 at 09:59
@TDP Just a comment / note to myself. We create an ad-hoc tally table with a max of 1MM rows. — John Cappelletti, Jan 11 '19 at 12:03

Eray Balkanli · Answer 2 · 2019-01-10T17:33:42.380

You can use charindex like below:

declare @temp table        
(val nvarchar(50))

insert into @temp values ('x(abc)')
insert into @temp values ('y(def)')
insert into @temp values ('z(ghi)')

SELECT 
SUBSTRING(val,CHARINDEX('(', val) + 1,CHARINDEX(')', val) - CHARINDEX('(', val) - 1) as Val
from @temp

Ref: SQL Select values inside parenthesis

EDIT: if it is one row including many values inside paranthesis, you need to split it based on comma first. For SQL 2008, I am using the split function specified in this link for years, thanks Andy: T-SQL split string

Create the function below:

CREATE FUNCTION dbo.splitstring ( @stringToSplit VARCHAR(MAX) )
RETURNS
 @returnList TABLE ([Name] [nvarchar] (500))
AS
BEGIN

 DECLARE @name NVARCHAR(255)
 DECLARE @pos INT

 WHILE CHARINDEX(',', @stringToSplit) > 0
 BEGIN
  SELECT @pos  = CHARINDEX(',', @stringToSplit)  
  SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)

  INSERT INTO @returnList 
  SELECT @name

  SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)
 END

 INSERT INTO @returnList
 SELECT @stringToSplit

 RETURN
END

And see this: SELECT * FROM dbo.splitstring('x(abc),y(def),z(ghi)')

This way, you can split your data based on comma and use the first charindex strategy I mentioned.

I use a similar parse/split function in the past. I was shocked at the performance gains in using the tally or xml approach. — John Cappelletti, Jan 10 '19 at 17:42
I’d upgrade to a non loop method but this answer isn’t incorrect :). http://www.sqlservercentral.com/articles/Tally+Table/72993/ — S3S, Jan 10 '19 at 17:46
Thank you for the answer. I've selected the other one though as it's not actually '(' and ')' that act as the delimiters, and I'd prefer not to use a loop as the actual string can be really long. — TDP, Jan 11 '19 at 10:02

Extract each string between each set of two different strings

2 Answers2