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

1

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.

2

Do the brackets always start after the name?

3

Yes the brackets are always after name. So it's everything left of first bracket i want as well as removing space before bracket.
Thanks

4

Can you try this

Select
RTRIM(SUBSTRING(employee_name, 0, charindex('(', employee_name, 0)))
From yourtable

you can use RTRIM to remove the spaces after the string but there might be performance issue.
You may get a better solution here.

5

Works a treat. Thanks loads

6

Since string "indexes" start at "1" in SQL Server, is there a reason why you used "0" for the string starting position or is it just a habit from other languages?

7

Just to ask the question, do you understand how and why it works for future such issues?

8

Just got used to it.
I would like to know what are the negative effects of using 0 as starting position.
As 0 as makes sure the beginning of string.

9

I was trying to get @ahmeds08 to explain by asking him the question about why the substring was started at 0. He didn't go for it. :grin:

So... is there a problem with the code? Let's find out. Please run the following against your original table so that we can see what the original problem was. Please post any names exactly as they appear if any are returned by the following code.

 SELECT TOP 5 
        Test = '|'+employee_name+'|'
   FROM yourtable
  WHERE CHARINDEX('(', employee_name) = 0
;

And, yes... I think you have a problem with your data that you don't know about and we're going to figure it out.