Selecting Substring SQL

Question

I need to select a substring that is found between (). The starting and ending position will vary, as well as the length of the substring. I have had moderate success with the following but not 100%.

It will work for some values but not for others, it return blanks and will also change the values capitalization format, in other words if value is 'TEST' it will display as 'Test'.

SELECT SUBSTRING(columnName, CHARINDEX('(', LEN(columnName)),
CHARINDEX(')', columnName) - CHARINDEX('(',columnName)) AS INPUT
FROM tableName

Update There is only 1 set of parentheses ()

There's no way that query will change the case of the substring, can you provide an example? — DavidG
– DavidG, Commented Apr 29, 2015 at 14:56
So can you clarify - what problem have you faced with your query? And how is it concerned to character case changing? — Andrey Korneyev
– Andrey Korneyev, Commented Apr 29, 2015 at 14:56
The character case is the least of my concerns. My biggest concern is that this query will select the correct substring between () for some values but for other it will completely miss. — Rene
– Rene, Commented Apr 29, 2015 at 15:00
What 'others'? Provide examples of what it's missing. The more we guess, the more chance we'll get it wrong. — John Bell
– John Bell, Commented Apr 29, 2015 at 15:04

DavidG · Accepted Answer · 2015-04-29 15:12:18Z

6

This will work provided you only have a single occurence of ( and ):

SELECT 
    SUBSTRING(columnName, 
              CHARINDEX('(', columnName)+1, 
              CHARINDEX(')', columnName) - CHARINDEX('(', columnName)-1)
FROM tableName

If you have values that do not have any (...) content, add this where clause:

WHERE    CHARINDEX('(', columnName) > 0 AND 
         CHARINDEX(')', columnName) > CHARINDEX('(', columnName)

edited Apr 29, 2015 at 15:12

answered Apr 29, 2015 at 15:04

DavidG

120k13 gold badges232 silver badges239 bronze badges

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

2 Comments

Rene Over a year ago

This query gives me the following error: Invalid length parameter passed to the LEFT or SUBSTRING function.

DavidG Over a year ago

Then not all your data has a (....) value. I've added a WHERE clause to cope with that.

Alex K. · Accepted Answer · 2015-04-29 15:12:40Z

2

To account for no, nested or incomplete ()

;with t(f) as (
    select 'aaa(bbb)ccc' union
    select 'aaa(bbbccc' union
    select 'aaabbb)ccc' union
    select 'aaa()ccc' union
    select '(aaa(?))ccc'
)

select f,
case when patindex('%(%)%', f) > 0 
    then substring(f, charindex('(', f ) + 1, (len(f) - charindex(')', reverse(f))) - charindex('(', f )) 
    else ''
end
from t

>>

f             (No column name)
aaa()ccc    
aaa(bbb)ccc   bbb
(aaa(?))ccc   aaa(?)
aaa(bbbccc  
aaabbb)ccc

answered Apr 29, 2015 at 15:12

Alex K.

177k32 gold badges276 silver badges299 bronze badges

Comments

gcbenison · Accepted Answer · 2015-04-29 15:13:03Z

1

In Postgres, you can do this using POSIX regular expression capture:

=> select substring('This (might) work' from '[(](.*)[)]');
 substring 
-----------
 might

It appears SQL server does offer regexp support, but I'm not familiar with it and I don't have a platform to run a test case. This particular example is tricky because you have to get the quoting of the delimiters () right.

edited Apr 29, 2015 at 15:13

answered Apr 29, 2015 at 15:05

gcbenison

12k4 gold badges46 silver badges82 bronze badges

2 Comments

DavidG Over a year ago

Not sure this is an answer, there's nothing like that in SQL Server!

DavidG Over a year ago

REGEX is only available when using a CLR based proc (i.e. a stored proc written in, for example, C#), not something I would recommend in this case.

Collectives™ on Stack Overflow

Selecting Substring SQL

3 Answers 3

2 Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related