SQL string manipulation [Get all text left of '(']

Question

I have some data which looks like so:

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

I am looking to transform this, so my data only displays:

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

I have tried:

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

with no luck.

I'm sure I'm missing something incredibly simple... Anyone care to enlighten?

KR, James.

Solution 1

I think you've just put a wrong character

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

Solution 2

You can;

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

(Remove + ' (' if needed; it allows for rows with no  (

Solution 3

This will return the company name whether or not there is a bracket, and will also handle cases where there is no space before the bracket:

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

SQL Fiddle Example