How to trim a string which changes at each result

Question

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

score 1 · Accepted Answer · answered Jun 26 '19 at 09:07

1

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.

answered Jun 26 '19 at 09:07

simon at rcl

7,326
1
17
24

Thank you this worked as expected. The progress equivalent is: Right(DelBatchNumber, Length(DelBatchNumber) - 4) – Jack Williams Jun 26 '19 at 09:41

score 1 · Answer 2 · answered Jun 26 '19 at 10:59

1

SQL Server has a function that explicitly does this, STUFF():

select stuff(Delivery_0.DelBatchNumber, 1, 4, '')

This replaces the first four characters with an empty string.

answered Jun 26 '19 at 10:59

Gordon Linoff

1,242,037
58
646
786

score 0 · Answer 3 · answered Jun 26 '19 at 09:04

try

select replace ('Job 321924','Job ','')

output

'321924'

first parameter is the string you want to change, second parameter is the string you want to replace, third parameter is the string you want to replace with.

So here i simply replaced 'Job ' by an empty string

Note that it returns a string and not an integer, you might need to use CONVERT/CAST if you want the result as integer

score 0 · Answer 4 · answered Jun 26 '19 at 09:28

0

You can try this, which tries to replace the 'Job ' string with empty string:

 SELECT 
      Delivery_0.DelProposedDate
    , Delivery_0.DelBatchNumber
    , REPLACE(Delivery_0.DelBatchNumber,'Job ','')  as 'JobID'
FROM SBS.PUB.Delivery Delivery_0

answered Jun 26 '19 at 09:28

Vahid Farahmandian

6,081
7
42
62

How to trim a string which changes at each result

4 Answers4