SQL Substring Selection

Question

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

It will work for some values, but not for others, it returns spaces and also changes the format of capitalization of values, in other words, if the value "TEST" is displayed as "Test".

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

Update There is only 1 set of parentheses ()

Answer 1

This will work if you have only one event ( ) :

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

If you have values ​​that have no content (...) , add this where clause:

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

Answer 2

For accounting 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 

Answer 3

In Postgres, you can do this with a POSIX regex:

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

It seems the SQL server offers regexp support , but I am not familiar with it, and I have no platform to run a use case. This specific example is complex because you must correctly quote the delimiters () .