CODEWITHSUNDEEP
sqlsql-server
baineschile
baineschile

Reputation: 149

Pull the rightmost string in parentheses

My data looks like this

ABCD.(123).(456)
789.(DEF) JKL.MNO
(PQ).(RS).(TUV)||(WXYz)

I am looking to pull the string from the RIGHTMOST parentheses brackets. Results would look like this

(456)
(DEF)
(WXYz)

The entire strings and strings within the parentheses can vary. Its some combination of substring and charindex, but i cannot figure it out.

Upvotes: 0

Views: 92

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

Such a question suggests a problem with the data structure -- that the string actually consists of multiple items. However, sometimes such string processing is necessary.

The following approach should work, assuming that a parenthesized component always exists:

select t.*, colparen
from t cross apply
     (values (right(col, charindex('(', reverse(col)) - 1)) v(colr) cross apply
     (values (charindex(colr, charindex(')', col) + 1)) v(colparen)

Upvotes: 1

kegs88
kegs88

Reputation: 37

select REVERSE(substring(reverse('ABCD.(123).(456)'),CHARINDEX(')',reverse('ABCD.(123).(456)')),CHARINDEX('(',reverse('ABCD.(123).(456)'))))

This should get you what you want

Upvotes: 0

John Cappelletti
John Cappelletti

Reputation: 81950

Since you are 2016, you can use String_Split() in concert with a Cross Apply.

Note: Use Outer Apply if there are no observations and you want to display a null value.

Example

Declare @YourTable table (SomeCol varchar(100))
Insert Into @YourTable values
('ABCD.(123).(456)'),
('789.(DEF).JKL.MNO'),
('(PQ).(RS).(TUV).(WXYz)')


Select B.Value
 From  @YourTable A
 Cross Apply (
               Select Top 1 *
                     ,RN=Row_Number() over (Order By (Select Null))
                From String_Split(A.SomeCol,'.')
                Where Value Like '(%)'
                Order by 2 Desc
             ) B

Returns

Value
(456)
(DEF)
(WXYz)

dbFiddle

Upvotes: 0

Related Questions