[Stackoverflow] [Progress OpenEdge ABL] How to trim a string which changes at each result

[Jack Williams]

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

Continue reading...