[SOLVED] Oracle SQL How to extract a string on the first occurrence between two specific fixed strings of "//"

Oracle SQL How to extract a string on the first occurrence between two specific fixed strings of "//"

I have data in an Oracle column in the below format:

Ch99// 4// Sub// 586915 16// jkc 12/12/22

And I need to extract the substring of "4" that will always be between the first two occurrences of "//".

So the output needed is:

I'm currently using ,regexp_substr(column, '//([^}]+)//', 1,1,NULL,1) but it's returning everything after the first "//"

Solution

I think the simplest thing is probably to use '//(.*?)//'. This matches any characters between // and //, but does it in a non-greedy way.

It is not clear why you are trying to use [^}]+ (matches one or more characters that are not a closing curly brace), since your question doesn't mention any requirements around curly braces.

A Fiddle of it working with your example:

SELECT regexp_substr(
 'Ch99// 4// Sub// 586915 16// jkc 12/12/22',
 '//(.*?)//', 
 1, 1, null, 1)
FROM dual

returns