CODEWITHSUNDEEP
sqloracle-database
C Radhasankar
C Radhasankar

Reputation: 51

Oracle Regexp_replace

i want to replace all the 'Spaces' with ' & ' excluding ' | ' in regexp_replace

For Ex: 'create | delete account' -> expect Output as 'create | delete & account'.

I m trying with the sql 

select regexp_replace('create | delete account','\s [^\s\|\s]',' & ') from dual

But i m doing something wrong here. Could anyone please help on it.

Upvotes: 0

Views: 301

Answers (2)

Aleksej
Aleksej

Reputation: 22969

If you need to replace every single space with the string ' & ', and there's no '&' in your starting string, you may avoid regular expressions:

select replace ( replace('create | delete account', ' ', ' & '), ' & | & ', ' | ') from dual

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522719

I think this is what you want:

select regexp_replace('create | delete account', '([^\|]) ([^\|])', '\1 & \2')
from dual

The logic here is to make the replacement whenever a space should occur between two non pipe characters. I capture these characters using parentheses, and then use them in the replacement via \1 and \2.

Upvotes: 3

Related Questions