CODEWITHSUNDEEP
sqlsql-server
Austin
Austin

Reputation: 31

Remove Training Parentheses in SQL Query

How do I remove the trailing parentheses from this query? The table below shows a few results of what the query outputs. I know I could just export the query into Excel and do it that way, but trying to work more on my SQL queries.

SELECT TOP (1000) [DepartmentNumber],
[DepartmentName],
[ChiefID],
[ChiefName],
[ChiefEmail],
[DelegateID],
substring(DelegateName, 1, CHARINDEX('(', DelegateName) - 1) DelegateName,
substring(DelegateName, CHARINDEX('(', DelegateName) + 1, LEN(DelegateName)) Accesss
FROM vwList

Edit: Here is the data before I do anything with it.

DelegateName
Smith, John M (Upload only)
Smith, Christine (Upload only)
Smith, Steven M. (View and Upload)
Smith, Kevin Lee (View)

Edit: Results from query

DelegateName Access
Smith, John M Upload only)
Smith, Christine Upload only)
Smith, Steven M. View and Upload)
Smith, Kevin Lee View)

Upvotes: 0

Views: 251

Answers (2)

S Shahar
S Shahar

Reputation: 77

If you know for certain that the "access" part of the value in DelegateName is wrapped in parenthesis, you can remove the last char from the result, or, if you know it will contain only one instance of ')', you can replace the closing parenthesis with empty string - as suggested.

substring(DelegateName, CHARINDEX('(', DelegateName) + 1, LEN(DelegateName)-1) Accesss

Upvotes: 0

Thom A
Thom A

Reputation: 95571

TRIM seems appropriate here:

SELECT LEFT(V.Delegate,CHARINDEX('(',V.Delegate)-2),
       TRIM(')' FROM STUFF(V.Delegate,1,CHARINDEX('(',V.Delegate),''))
FROM (VALUES('Smith, John M (Upload only)'),
            ('Smith, Christine (Upload only)'),
            ('Smith, Steven M. (View and Upload)'),
            ('Smith, Kevin Lee (View)'))V(Delegate);

db<>fiddle

Upvotes: 1

Related Questions