CODEWITHSUNDEEP
oracle-database
GianIsTheName
GianIsTheName

Reputation: 187

Removing the Character before Dash

I am to extract characters from a string before and after the dash (-). So far, I was able to use this code below to return all the numbers before dash:

SELECT 
INSTR('100-7', '-'),
SUBSTR('100-7', 1, INSTR('100-7', '-')-1)
FROM dual;

Now I am trying to find a way to return all the characters after dash. So if I have a string:

20-150

THe query should only return me 150. Is there anyway I can do this? By the way, I am using Oracle. Thanks guys :)

Upvotes: 2

Views: 13497

Answers (4)

user14098194
user14098194

Reputation: 1

The query should be something like this.

select substr('P_A_VA1234_1_EE',(INSTR('P_A_VA1234_1_EE','_',1,2)+1),(INSTR('P_A_VA1234_1_EE','_',1,4)-(INSTR('P_A_VA1234_1_EE','_',1,2)+1)))original from dual

select substr(shipment_gid,(INSTR(shipment_gid,'_',1,2)+1),(INSTR(shipment_gid,'_',1,4)-(INSTR(shipment_gid,'_',1,2)+1)))original from dual

SUBSTR(S.SHIPMENT_GID,8,8) LOADID,

select substr(shipment_gid,(INSTR(shipment_gid,'_',1,2)+1),(INSTR(shipment_gid,'_',1,4)-(INSTR(shipment_gid,'_',1,2)+1)))load,shipment_gid from shipment where shipment_gid like '%DHL/CLS.CLS_CV_%';

SUBSTR(S.SHIPMENT_GID,(INSTR(S.SHIPMENT_GID,'_',1,2)+1),(INSTR(S.SHIPMENT_GID,'_',1,4)-(INSTR(S.SHIPMENT_GID,'_',1,2)+1)))

Upvotes: -1

Girish Acharya
Girish Acharya

Reputation: 99

using REGEXP_SUBSTR :

SELECT REGEXP_SUBSTR ('20-15055555555', '[^-]+', 1, 2) rep
  FROM DUAL;

you can try this

Upvotes: 0

peterm
peterm

Reputation: 92785

This will return everything after the dash

SELECT SUBSTR(value, INSTR(value, '-')+1) invalue
  FROM (SELECT '20-150' value FROM dual) t;

Output:

| INVALUE |
-----------
|     150 |

sqlfiddle

And putting both together

SELECT SUBSTR(value, 1, INSTR(value, '-') - 1) lvalue, 
       SUBSTR(value, INSTR(value, '-') + 1) rvalue
  FROM (SELECT '20-150' value FROM dual) t;

Output:

| LVALUE | RVALUE |
-------------------
|     20 |    150 |

sqlfiddle

Upvotes: 8

Grisha Weintraub
Grisha Weintraub

Reputation: 7986

Using REGEXP_REPLACE :

SELECT REGEXP_REPLACE('20-150','(.*)-','') 
FROM dual;

Upvotes: 1

Related Questions