Jump to content

Using Charindex to get left of a charcter

Recommended Posts

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 

Link to post
Share on other sites
  • 11 months later...

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.

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...