0

I am working in Oracle to fix some text. The issue is that sentences in my data have words where sentences aren't separated by spaces. For example:

  1. Sentence without space.Between sentences

  2. Sentence with question mark?Second sentence

I've tested the following replace statement in regex101 and it seems to work out there, but I can't pinpoint why it's not working in Oracle:

regexp_replace(review_text, '([^\s\.])([\.!\?]+)([^\s\.\d])', '\1\2 \3')

This should allow me to look for sentence-separating periods/exclamation points/question marks (single or grouped) and add the necessary space between sentences. I realize that there are other ways that sentences can be separated, but what I have above should cover a large majority of the use cases. The \d in the third capture group is to make sure that I'm not accidentally changing numeric values like "4.5" to "4. 5".

Before test group:

Sentence without space.Between sentences
Sentence with space. Between sentences
Sentence with multiple periods...Between sentences
False positive sentence with 4.5 Liters
Sentence with!Exclamation point
Sentence with!Question mark

After changes should look like this:

Sentence without space. Between sentences
Sentence with space. Between sentences
Sentence with multiple periods... Between sentences
False positive sentence with 4.5 Liters
Sentence with! Exclamation point
Sentence with! Question mark

Regex101 link: https://regex101.com/r/dC9zT8/1

While all changes work as expected from regex101, my issue is that I'm getting in Oracle is that my third and fourth test cases aren't working as intended. Oracle isn't adding a space after the multiple period (ellipses) group, and regexp_replace is adding a space for "4.5". I'm not sure why this is the case, but perhaps there's some peculiarity about Oracle regexp_replace that I'm missing.

Any and all insight is appreciated. Thanks!

Improve this question
2
  • My guess would be that it's the global match (g flag) that's turned on in regex101 and not in Oracle. Commented Sep 6, 2016 at 16:32
  • Global occurrence is something I didn't think of, but even when using setting occurrence = 0 in Oracle, I'm still getting the same issues. Commented Sep 6, 2016 at 17:24

1 Answer 1

Reset to default
2

This may get you started. This will check for .?! in any combination, followed by zero or more spaces and by an uppercase letter, and it will replace "zero or more spaces" by exactly one space. This will not separate a decimal number; but it will miss sentences that begin with anything other than an uppercase letter. You may start adding conditions - if you run into difficulty please write back and we'll try to help. Referring to other regex dialects may be helpful, but it may not be the fastest way to get your answer.

with
     inputs ( str ) as (
       select 'Sentence without space.Between sentences'           from dual union all
       select 'Sentence with space. Between sentences'             from dual union all
       select 'Sentence with multiple periods...Between sentences' from dual union all
       select 'False positive sentence with 4.5 Liters'            from dual union all
       select 'Sentence with!Exclamation point'                    from dual union all
       select 'Sentence with!Question mark'                        from dual
     )
select regexp_replace(str, '([.!?]+)\s*([A-Z])', '\1 \2') as new_str
from   inputs;

NEW_STR
-------------------------------------------------------
Sentence without space. Between sentences
Sentence with space. Between sentences
Sentence with multiple periods... Between sentences
False positive sentence with 4.5 Liters
Sentence with! Exclamation point
Sentence with! Question mark

6 rows selected.
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thanks mathguy - what you've written is logically sound. I'll apply what you've given (though I'll also use lowercase a-z as well) and check if there's anything missing.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.