CODEWITHSUNDEEP
oracle-database
PBD
PBD

Reputation: 111

Extract sub-string after match in oracle

I have string like Order@Confirm@@@@2791 i wanted to fetch 2791 after @@@@ delimiter. I tried below one not getting exact sub-string what i am expecting to return.

SELECT regexp_substr('Order @ Confirm @@@@2791','[^@@@@]+',1,2) regexp_substr
FROM dual;

I would like to return 2791 from above query.

Upvotes: 1

Views: 303

Answers (2)

Gary_W
Gary_W

Reputation: 10360

SELECT regexp_substr('Order @ Confirm @@@@2791','@@@@(.*)$',1,1, null, 1) regexp_substr
FROM dual;

If you want to restrict the match to digits:

SELECT regexp_substr('Order @ Confirm @@@@2791','@@@@(\d+)$',1,1, null, 1) regexp_substr
FROM dual;

regexp_replace works too:

SELECT regexp_replace('Order @ Confirm @@@@2791','.*?@@@@(\d+)$', '\1') regexp_replace
FROM dual;

Note with regexp_substr() if a match is not found NULL is returned and with regexp_replace() if a match is not found the original string is returned.

Upvotes: 3

user5683823
user5683823

Reputation:

You don't need regular expressions for this.

SELECT substr('Order @ Confirm @@@@2791', 
              instr('Order @ Confirm @@@@2791', '@@@@') + 4) as your_substr
FROM dual;

Upvotes: 2

Related Questions