SQL string manipulation [Get all text left of '

问题:

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.

回答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

回答2:

You can;

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

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

回答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

回答4:

You cam simply try this.

Create a new table and insert the data as below scripts. Then execute the select query. You will get required output.

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