SQL: Extract every substring found between every brackets in that string using a SELECT statement

Question

I have a Description column that contains a long string with varying length and want to extract the content found between every brackets in that string.

I am able to extract the content found in the first pair of brackets but not sure how to tackle cases where more pairs of brackets are found.

I would like to use a SELECT statement only if possible.

My query so far looks like this

SELECT SUBSTRING (Description, CHARINDEX('[', Description)+1, CHARINDEX(']', Description)-CHARINDEX('[', Description)-1)
FROM [MyTable].[Description]
WHERE Description like '%(%'

So for example with the following data

Description (column)

Row 1 blablablablalbaalala (blibliblobloblo) blalblalala (blululublululu)

My query will only return 'blibliblobloblo' but I also want 'blululublululu'

Are you looking for text that contains brackets [ ] or parentheses ( )? — Jason
– Jason, Commented Jul 13, 2017 at 14:06
Thank you @rolanda_medina I will check this one out. And yes Jason I was initially looking for square brackets but I thought if I changed it to parentheses it would make the example easier but then I forgot to change the square bracket in my example — Manny
– Manny, Commented Jul 14, 2017 at 11:02

gbn · Accepted Answer · 2017-07-13 14:08:17Z

2

SQL Server 2016 and above

DECLARE @foo varchar(100) =  'lablablablalbaalala (blibliblobloblo) blalblalala (blululublululu)'

SELECT
    LEFT(value, CHARINDEX(')', value)-1)
FROM
    STRING_SPLIT(@foo, '(')
WHERE
    value LIKE '%)%'

answered Jul 13, 2017 at 14:08

gbn

434k84 gold badges602 silver badges689 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Manny Over a year ago

sorry I have 2014. my bad I should have mentioned it

John Cappelletti · Accepted Answer · 2017-07-13 14:32:56Z

If you are open to a TVF (Table-Valued Function).

Tired of extracting strings (charindex,left,right,...) I modified a parse function to accept two non-like delimiters. In your case this would be ( and ).

Example

Declare @YourTable table (ID int,SomeCol varchar(max))
Insert Into @YourTable values
(1,'blablablablalbaalala (blibliblobloblo) blalblalala (blululublululu)')

Select A.ID
      ,B.*
 From  @YourTable A
 Cross Apply [dbo].[udf-Str-Extract](A.SomeCol,'(',')') B

Returns

ID  RetSeq  RetPos  RetVal
1   1       23      blibliblobloblo
1   2       53      blululublululu

The UDF if Interested

CREATE FUNCTION [dbo].[udf-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].[udf-Str-Extract] (@String,'[[',']]')
*/

Collectives™ on Stack Overflow

SQL: Extract every substring found between every brackets in that string using a SELECT statement

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related