Search code examples
sqlstringsybaseadvantage-database-server

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)
Solution

  • 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)