CODEWITHSUNDEEP
sqlsql-serversubstringsql-server-2016charindex
JGW
JGW

Reputation: 334

How do I use SUBSTRING and CHARINDEX in SQL to return only the right part of a field contained within brackets if multiple brackets are in the field?

I have a column of data that contains varchar data and I've been trying to return only the section contained with brackets using CHARINDEX and SUBSTRING :

column data: 'this is an (example)'

I use the following code to return 'example' from the above data:

SELECT SUBSTRING (column,  CHARINDEX('(', column)+1 ,   CHARINDEX(')', column)- CHARINDEX('(', column)-1 )

This works fine, however, in some instances, the data in the field has multiple occurrences of data between two brackets:

- 'this (is) an (example)'

This means that my code above returns 'is'. In my data, what I want to return is the data in the right most set of brackets:

etc

Upvotes: 0

Views: 6462

Answers (3)

Rajat
Rajat

Reputation: 5803

If the text/number itself don't include ( or ), this should work too

select replace(reverse(left(reverse(str_col), charindex('(', reverse(str_col)) -1)),')','')
from test;

Upvotes: 1

VBoka
VBoka

Reputation: 9083

This is one way to go also: Here is the DEMO

select reverse(
          substring(
             reverse(str_col)
             , CHARINDEX(')', reverse(str_col))+1
             , CHARINDEX('(', reverse(str_col))-2 
          )
       )
from test;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269693

In all your examples, the last character is a ). If this is always true, then the simplest method is probably:

select replace(stuff(v.str, 1, len(v.str) - charindex('(', reverse(v.str)) - 1, ''), ')', '')
from (values ('this (is) definitely (not) a (number)')) v(str);

A more general solution is:

select v.str,
       replace(stuff(v2.str, 1, len(v2.str) - charindex('(', reverse(v2.str) + '(') + 1, ''), ')', '')
from (values ('this (is) definitely (not) a (number)'),
             ('this (is) definitely (not) a (number) alas'),
             ('no parens')
     ) v(str) cross apply
     (values (left(v.str, len(v.str) - charindex(')', reverse(v.str) + ')') + 1))) v2(str);

Upvotes: 1

Related Questions