Using Charindex to get left of a charcter

[bk171]

I have a field called subjects  and the data looks like this ALJ Diane Davis - WCF

I am trying to get all the data to left of the "-"

I am using Advantage SQL which I am new too.  IF i was using T-SQL i would be using something like this:  left(appts.subject,charindex('-',appts.subject)

 

Thanks in advance 

[justsomeguy]

Here's the documentation:

https://devzone.advantagedatabase.com/dz/webhelp/Advantage9.1/string_functions.htm

[shaili_shah]

I think there is one more function for doing that :

SELECT STUFF('ALJ Diane Davis - WCF', 17, 21, 'WCF -')

Let me know if you are doing as above😊. 

Edited February 6, 2020 by shaili_shah

[dsonesuk]

THAT! would work only, if ALL! column values ended exactly with '- WCF', 17 characters from the left. Also, main problem here, he does not want to replace the string, left of '- WCF', but retrieve it! 

Basically he wants from 1st character position of 0 to the position of  '-'

LEFT( subjects, POSITION( '-' IN subjects ) ) 

if value contained in column subjects is 'ALJ Diane Davis - WCF', Find position of '-' within it, using POSITION() which will be 17, and return string characters from left LEFT() to value giving using POSITION(). It then does not matter what the value length is.