I have a string like this x=y AND z=y AND p=q , I need to replace every (space)AND(space) pattern with * so that the original string turns into x=y*z=y*p=q. I'm using Regular expression for this cause the important thing is that there might by more that one space before and after each AND .

I've written two different queries for this


 select regexp_replace('x=y AND z=y AND p=q','[[:space:]]AND[[:space:]]','*')
 from dual;


 select regexp_replace('x=y AND z=y AND p=q', '\s+AND\s+', '*')
 from dual;

I want to know :

  1. Are there better ways to do that?
  2. What is the main difference between these two queries? I mean[[:space:]] and \s? The important thing is that I want to know In what sort of condition these two queries will produce different result?

Thanks in advance

  • 1
    Don't know if this holds for Oracles implementation, but in other languages \s will match whitespace (more than just space, \t, \r etc.), whereas [[:space:]] will only match space – Lennart Jul 7 '20 at 10:36
  • 1
    I'd say both have same meaning. \s it "old" way, while [[:space:]] is Perl extension, now being part of Posix Standard. – ibre5041 Jul 7 '20 at 14:05
  • @ibre5041 Thanks. So you say both queries produce same result in different conditions? – Pantea Jul 7 '20 at 17:28

The Oracle documentation confirms that the POSIX expression [[:space:]] is equivalent to the Perl-style \s. I personally prefer \s because it's shorter, and also supported by many text editors.

In your example you have [[:space:]] (one whitespace character) but \s+ (one or more whitespace characters). Maybe that should have been [[:space:]]+.


Those anchors are used whenever you want to have something very general as it says in the regular expression processors they care about "Any space character" but then the definition in POSIX is more elaborated and says "space, tab, newline". It depends on your expression of how it is written because technically using these two alternatives should not introduce any bug or difference in behavior.

\\s → any space character
\\S → anything but a space character
[[:space:]] → space, tab, newline etc.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.