Using Charindex to get data left of a character

问题:

I have a field called subjects  and the data looks like this:

ALJ Diane Davis - WCF

I want my end result to be:

ALJ Diane Davis

I am trying to get all the data to left of the "-" I am using Advantage SQL which I am new too. 

The example below using the RIGHT function gets me everything to the right which works if i wanted that, but i dont always know the exact number of characters for the way that i am wanting my data to end up like.

Thanks in advance 

left(appts.subject,charindex('-',appts.subject)

left(appts.subject,char('-',appts.subject)-1)

right(rtrim(appts.subject),6)

回答1:

Doesn't this work?

left(appts.subject, charindex('-', appts.subject) - 1)

If this fails because not all subjects have a -, then:

left(appts.subject, charindex('-', appts.subject + '-') - 1)

The above works in Sybase. In Advantage SQL, I think you need location:

left(appts.subject, locate('-', appts.subject) - 1)

回答2:

This should give you result. Locate is the function works in Adavantage-Sql. You can use this link

Function- LOCATE( str1, str2[, start] )

Return integer location (1-based) of str1 in str2, with optional start starting point. If str1 is not found in str2, 0 is returned.

SELECT SUBSTRING('ALJ Diane Davis - WCF', 1, locate('-', 'ALJ Diane Davis - WCF') - 1)