SQL manipulation [Get all text to the left of '(']

Question

I have some data that look like this:

SourceOfBooking ---------------- Company1 (Foo) Company2 (Bar) Company3 (Foo1) Company4 (Foo2) 

I want to convert this, so my data is only displayed:

 SourceOfBooking ---------------- Company1 Company2 Company3 Company4 

I tried:

 LEFT(SourceOfBooking, CHARINDEX(';', SourceOfBooking) ) 

no luck.

I'm sure I'm missing something incredibly simple ... Does anyone want to enlighten?

KR, James.

Answer 1

I think you just put the wrong character

 case when CHARINDEX('(', SourceOfBooking) > 0 then rtrim(left(SourceOfBooking, CHARINDEX('(', SourceOfBooking) - 1)) else SourceOfBooking end 

Answer 2

You can:

 LEFT(SourceOfBooking, CHARINDEX(' (', SourceOfBooking + ' (') - 1) 

(If necessary, delete + ' (' , it allows you to create lines without (

Answer 3

This will return the company name, whether there is a bracket or not, and will also handle cases when there is no space in front of the bracket:

 select case when CHARINDEX('(', SourceOfBooking) > 0 then RTRIM(LEFT(SourceOfBooking, CHARINDEX('(', SourceOfBooking) - 1)) else SourceOfBooking end from Table1 

SQL script example

Answer 4

You smoke, just try this.

Create a new table and insert the data as shown below. Then run the select query. You will get the desired result.

 create table teststring (name varchar(20)) insert into teststring values ('ashish (123)') insert into teststring values ('ashish jain (123)') select substring(name,1,charindex('(',name)-1)abc ,name from teststring 

Answer 5

This will achieve the same without an additional case when.

 SELECT SourceOfBooking,LEFT(SourceOfBooking,LEN(SourceOfBooking)-CHARINDEX('(',SourceOfBooking)+1)