CODEWITHSUNDEEP
sqlsql-serverreplacecharindex
Carle Veall
Carle Veall

Reputation: 1

How to remove all text inside brackets in sql server and return only text to the left of this

I have for example a string of text as below

John Smith (Boss:tom collins) (onleave)

But I want to return only John Smith with no space after John Smith. John Smith is stored inside an employee_name field Is there a way to do this in sql server? There can be more than one instance of the brackets

I tried using the below

left(A.[employee_name], CHARINDEX('(', A.[employee_name]) - 1)

but it returned following error in SSMS

Invalid length parameter passed to the LEFT or SUBSTRING function. The statement has been terminated.

Upvotes: 0

Views: 82

Answers (1)

Nick
Nick

Reputation: 147216

Your issue is that when there are no ( in the employee_name field, CHARINDEX returns 0 and so the index passed to LEFT is invalid (-1). You need to check for the presence of ( first. For example:

select CASE WHEN CHARINDEX('(', A.[employee_name]) > 0
            THEN TRIM(LEFT(A.[employee_name], CHARINDEX('(', A.[employee_name]) - 1))
            ELSE TRIM(A.[employee_name])
       END
from A

Demo on dbfiddle.uk

Upvotes: 0

Related Questions