1

I have a string like this x=y and g=h and u=1 and I need to replace each (space)and(space) with ,. I'm using the powerful regular_expression for this but it does not give me the desired result.

select regexp_replace('x=y and g=h and u=1','(^[[:space:]]*)AND(^[[:space:]]*)', ',')
from dual;

I was wondering if you could help me out here. thanks in advance.

1

If the format is always exactly one space either side of a lowercase and, then you don't need regexp_replace as plain old replace will do it:

with demo (str) as
      ( select 'x=y and g=h and u=1' from dual )
select str
     , replace(str, ' and ', ',') as plain_replace
from   demo;

STR                 PLAIN_REPLACE
------------------- -------------------
x=y and g=h and u=1 x=y,g=h,u=1

A regex is needed if the number of spaces can vary:

with demo (str) as
      ( select 'x=y  and g=h and   u=1' from dual )
select str
     , replace(str, ' and ', ',') as plain_replace
     , regexp_replace(str, '\s+and\s+', ',') as one_or_more_spaces
from   demo;
STR                     PLAIN_REPLACE          ONE_OR_MORE_SPACES
----------------------- ---------------------- ---------------------------
x=y  and g=h and   u=1  x=y ,g=h,  u=1         x=y,g=h,u=1

The + means one or more, in contrast to * which means zero or more. For example I assume you don't want Band in Spandex replaced with B,in Sp,ex.

To be on the safe side, I've used the Perl-style \s (whitespace) operator rather than a literal space character, as this will also match any tab characters.

| improve this answer | |
1
select regexp_replace('x=y and g=h and u=1','([[:space:]]*)and([[:space:]]*)', ',')
from dual;

REGEXP_REPL
-----------
x=y,g=h,u=1
| improve this answer | |

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.