Removing apostrophes from regex pattern

Question

I'm a little new to regex and have written a simple regex to retrieve the word "Preview" from the from the string below.

Time: '333333333333333333316:06:55 -232323' Permission: 'Preview' Teacher name: '4444444444444ffffffffffff' Student: 'cccccccccccssssssssss' Exam score: 'eeeeeeeeeeeA'

This is my regex expression below

Permission:\s?'([a-zA-z]+)'\s?Teacher name

I want to recreate my regex expression NOT to have apostrophes in it.

I plan to use this regex in REGEX_SUBSTR expression in an oracle query and it seems not to consume my regex with apostrophe very well.

How do i do that?

I know regex has many formats, so if anyone knows a better pattern, please feel free to suggest.

Please [format your question](https://stackoverflow.com/help/formatting). — Alex Poole, Aug 18 '17 at 17:20
FYI: note that [`[a-zA-z]` matches more than just letters](https://stackoverflow.com/a/29771926/3832970). — Wiktor Stribiżew, Aug 18 '17 at 17:20

Alex Poole · Answer 1 · 2017-08-18T17:35:20.877

You can use your current regex pattern (corrected as per Wiktor's comment!) by escaping the single quotes:

regexp_substr(<your string>, 'Permission:\s?''([a-zA-Z]+)''\s?Teacher name')

or more cleanly by using the alternative quoting mechanism:

regexp_substr(<your string>, q'[Permission:\s?'([a-zA-Z]+)'\s?Teacher name]')

which lets you leave the single quotes as they were originally, and put the whole thing inside a set of delimited single quotes, using a delimiter that doesn't appear in your actual content - I've used square brackets here, so q'[ ... ]'. That lets whatever is inside, the ... part, include unescaped single quotes.

Demos:

with t (str) as (
  select q'[Time: '333333333333333333316:06:55 -232323' Permission: 'Preview' Teacher name: '4444444444444ffffffffffff' Student: 'cccccccccccssssssssss' Exam score: 'eeeeeeeeeeeA']' from dual
)
select regexp_substr(str, 'Permission:\s?''([a-zA-Z]+)''\s?Teacher name')
from t;

REGEXP_SUBSTR(STR,'PERMISSION:\S?'
----------------------------------
Permission: 'Preview' Teacher name

or:

with t (str) as (
  select q'[Time: '333333333333333333316:06:55 -232323' Permission: 'Preview' Teacher name: '4444444444444ffffffffffff' Student: 'cccccccccccssssssssss' Exam score: 'eeeeeeeeeeeA']' from dual
)
select regexp_substr(str, q'[Permission:\s?'([a-zA-Z]+)'\s?Teacher name]')
from t;

REGEXP_SUBSTR(STR,Q'[PERMISSION:\S
----------------------------------
Permission: 'Preview' Teacher name

To just get the word 'Preview' you can use regexp_replace() with a back-reference:

with t (str) as (
  select q'[Time: '333333333333333333316:06:55 -232323' Permission: 'Preview' Teacher name: '4444444444444ffffffffffff' Student: 'cccccccccccssssssssss' Exam score: 'eeeeeeeeeeeA']' from dual
)
select regexp_replace(str,
  '(.*Permission:\s?'')([a-zA-Z]+)(''\s?Teacher name.*)', '\2', 1, 1)
from t;

REGEXP_
-------
Preview

or

with t (str) as (
  select q'[Time: '333333333333333333316:06:55 -232323' Permission: 'Preview' Teacher name: '4444444444444ffffffffffff' Student: 'cccccccccccssssssssss' Exam score: 'eeeeeeeeeeeA']' from dual
)
select regexp_replace(str,
  q'[(.*Permission:\s?')([a-zA-Z]+)('\s?Teacher name.*)]', '\2', 1, 1)
from t;

REGEXP_
-------
Preview

Removing apostrophes from regex pattern

1 Answers1