0

I have a simple question I cant find the answer for:

Is this:

regexp_replace(somecolumn, someregex)

The same as the following?

regexp_replace(somecolumn, someregex, NULL)
Improve this question
4
  • 1
    Good question. Often Oracle documentation looks like it's written by morons. Case in point - the syntax diagram for regexp_replace shows that the replace_string argument is optional, but doesn't say anywhere what the behavior is if that argument is left out. Your guess is 100% correct, but you shouldn't have to guess, that should be crystal clear in the documentation. Commented Nov 18, 2021 at 18:31
  • I agree haha, I asked this question because im running some queries I have in an oracle project, in an hsql database, and some oracle queries are not working. I noticed that regexp_replace(somecolumn, someregex, NULL) doesn't work on hsqldb, but regexp_replace(somecolumn, someregex) does work, hence my question. Commented Nov 18, 2021 at 18:37
  • Would the query in hsql (whatever that is) work if you used an empty string instead? Oracle - in direct violation of the SQL standard - treats empty string the same as null; what you REALLY want in your "replace", if things were done properly, is to replace with empty string (not with "unknown value" which is what null is, more or less). Commented Nov 18, 2021 at 19:58
  • Yes, it works with an empty string! Thanks for the explanation! Commented Nov 19, 2021 at 13:22

1 Answer 1

Reset to default
2

Yes, they're the same. The documentation for REPLACE is more explicit about this behavior:

If replacement_string is omitted or null, then all occurrences of search_string are removed.

But REGEXP_REPLACE behaves the same way. The third argument replace_string replaces whatever matches the pattern; if it's omitted or null, all matching parts of the string are removed.

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

1 Comment

Thank you so much for the clarification!

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.