CODEWITHSUNDEEP
regexoracle-database
Aayush Rajopadhyaya
Aayush Rajopadhyaya

Reputation: 103

I am looking to extract a substring from a given set of string using REGEXP_SUBSTR

This is the provided string

MB/123456789/Load ABC,801774/123456789

Here i am required to extract the string after comma and before the slash. i.e.801774

For now i have used this query.

select TRAN_PARTICULAR,regexp_substr(regexp_substr(TRAN_PARTICULAR,'[^,]+',1,2),'[^/]+') from VW_TRANSACTION_SEARCH

This is working fine yet is there any alternative for optimizing this query.

Upvotes: 1

Views: 39

Answers (1)

Wiktor Stribiżew
Wiktor Stribiżew

Reputation: 626689

You may use

regexp_substr('MB/123456789/Load ABC,801774/123456789',',([^,/]+)/',1, 1, NULL,1)

See the online Oracle demo and a regex demo.

Here, ,([^,/]+)/ matches ,, then captures into Group 1 any one or more chars other than , and / and then matches /. The last 1 argument tells REGEXP_SUBSTR to return the value in Group 1.

If you are sure there are only digits there, use ',(\d+)/' or ',([0-9]+)/'.

Upvotes: 1

Related Questions