String segments within a record. 1

[isnomagic]

I have column records within a General ledger table which relate to the line postings from an invoice.

This column has records like this
GL Inv. Shipment:139/0-6
GL Inv. Shipment:139/0-7
GL Inv. Shipment:139/0-8
GL Inv. Shipment:139/0-9
GL Inv. Shipment:139/0-10
GL Inv. Shipment:723/0-1
GL Inv. Shipment:723/0-2
GL Inv. Shipment:723/0-3
GL Inv. Shipment:1885/0-1
GL Inv. Shipment:1885/0-2
GL Inv. Shipment:1885/0-3

Now to join with another table say SALES I need to get the order number like 139, 723 and 1885 in these cases from this string. These are the numbers after the colon : and before the forward slash /. The other part I need to get is the extension number relating to the order number. In each of the above records it is the 0 (could be 1 or more also) after the forward slash / and before the dash -. These are the two primary keys I need to join with the SALES table. Any ideas how to extract these segments as they are variable length yet defined by the separators as such. In pick/unix I use to do it as a group extract.

 

[JarlH]

Use POSITION to find the part of the string you are looking for and SUBSTRING to extract that specific part. Like:

SUBSTRING(col1 FROM POSITION(':' IN col1)+1
FOR POSITION('/' IN col1)-POSITION(':' IN col1))


This is how SUBSTRING works:
SUBSTRING (source-string FROM start-position FOR string-length)

source-sting is in your case the column.
start-position is where the substring starts.
string-length is the number of extracted characters.


/Jarl