[SOLVED] How to trim a string which changes at each result

How to trim a string which changes at each result

I have data which contains a date and a batch number. I need to be able to trim down the batch number removing the leading word "Job " from each. The issue i have is each result is different and is of a different length also.

To try and deal with this i have tried to use LEFT and CHARINDEX to trim it but get a syntax error back. Because i am using MS Query on a open edge v10 progress odbc database it is not clear as to what the issue is. Below is the code i have produced.

    SELECT 

      Delivery_0.DelProposedDate
    , Delivery_0.DelBatchNumber
    , LEFT(Delivery_0.DelBatchNumber,CHARINDEX(' ',Delivery_0.DelBatchNumber)-1) as 'JobID'

FROM SBS.PUB.Delivery Delivery_0

Currently the data looks like this:

DelProposedDate               DelBatchNumber
05/05/2017                    Job 321924
08/02/2019                    Job 356812/4
29/03/2017                    Job 328585

I am trying to get it to look like this:

DelProposedDate               DelBatchNumber             JobID
05/05/2017                     Job 321924                321924
08/02/2019                     Job 356812/4              356812/4
29/03/2017                     Job 328585                328585

Solution

You want to exclude the left-most 4 characters ('Job '). This is the same a showing the right-most x characters where x = length-of-string - 4. I'm not that conversant with Progress' variant of SQL, but something like:

Right(DelBatchNumber, Len(DelBatchNumber) - 4)

would do it. You may need to substitute the Progress equivalent of Right and Len, and possibly check the order of the parameters Right takes.