oracle regexp_substring return only part of pattern

Question

Regexp_substr return only part of searching pattern.

  SELECT
  REGEXP_SUBSTR('im searching value beginning from keyword with word "number" and digits after it like number № 3 in the string',
                '(number.{0,4}\d{1,2})')

                "REGEXP_SUBSTR"
  FROM DUAL

Its returning "number №", without digits. However, search works fine: without "№ 3" in sample code it return none

Your query doesn't even work for me. What are you trying to do here? — Tim Biegeleisen
– Tim Biegeleisen, Commented Nov 11, 2019 at 16:08
There can be differences between regex tools depending on the environment. A demo in Regex 101 is not the same as Oracle SQL's regex engine. — Tim Biegeleisen
– Tim Biegeleisen, Commented Nov 12, 2019 at 3:54

GMB · Accepted Answer · 2019-11-11 16:09:16Z

1

I think that this funky № character may actually count for more than one character for Oracle. Extending .{0,4} to .{0,5} seems to work:

SELECT
  REGEXP_SUBSTR(
      'im searching value beginning from keyword with word "number" and digits after it like number № 3 in the string',
      '(number.{0,5}\d{1,2})'
  ) "REGEXP_SUBSTR"
  FROM DUAL

Demo on DB Fiddle

answered Nov 11, 2019 at 16:09

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user1585476 Over a year ago

Yes, something wrong with unicode-related area. Not works on original, but on sample is :(

Collectives™ on Stack Overflow

oracle regexp_substring return only part of pattern

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related