ORACLE sql Substr / Instr-CodePudding

I have a column within a table that has PO-RAILCAR. I need to split this column into two. I write the following query and it does exactly what I want. However, the results come back with the dash. How do I write it to return the values as they are without the dashes?

 SELECT INVT_LEV3, SUBSTR(INVT_LEV3,1,INSTR(INVT_LEV3,'-')) AS PO,
                   SUBSTR(INVT_LEV3,INSTR(INVT_LEV3,'-')) AS Railcar
 FROM C_MVT_H
 WHERE INVT_LEV4 = 'G07K02129/G07K02133'

This is what I get: First column is the column I need to split. The second and third look perfect but I need the dash removed

Column 1: 110799P-FBOX50553 Column2: 110799P- Column3:-FBOX505536

CodePudding user response：

The problem is occurring because INSTR is giving you the position of the '-' within the text. To fix this you can just add or subtract 1 from the position returned.

Your current query:

SELECT INVT_LEV3, SUBSTR(INVT_LEV3,1,INSTR(INVT_LEV3,'-')-1) AS PO, SUBSTR(INVT_LEV3,INSTR(INVT_LEV3,'-') 1) AS Railcar FROM C_MVT_H WHERE INVT_LEV4 = 'G07K02129/G07K02133'

Proposed new query

SELECT INVT_LEV3, SUBSTR(INVT_LEV3,1,INSTR(INVT_LEV3,'-')) AS PO, SUBSTR(INVT_LEV3,INSTR(INVT_LEV3,'-')) AS Railcar FROM C_MVT_H WHERE INVT_LEV4 = 'G07K02129/G07K02133'