Oracle Regexp_replace

Question

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.

If you have a sequence of spaces, do you need to replace every space with ' & '? Also, is it possible that you have the character '&' in your starting string? — Aleksej
– Aleksej, Commented Dec 29, 2016 at 13:48

Tim Biegeleisen · Accepted Answer · 2016-12-29 13:54:26Z

3

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.

answered Dec 29, 2016 at 13:54

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

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

4 Comments

Gurwinder Singh Over a year ago

Use ([^\|])\s+([^\|]) for the case of more than 3 spaces. +1 though.

Tim Biegeleisen Over a year ago

@GurwinderSingh Thanks for the comment and upvote :-)

C Radhasankar Over a year ago

@ Tim Biegeleisen - Thank you .. It solved .. I liked the way you seen the requirement (ie) 'Replace space between two non pipe character'

user5683823 Over a year ago

This may work for the OP, but how about text like this: 'a | cos(b) exp(c) | z |\lambda|'? The pipe symbol may appear with space on one side only, and that space SHOULD be replaced with ' & ' according to the requirement.

Aleksej · Accepted Answer · 2016-12-29 14:02:58Z

0

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

edited Dec 29, 2016 at 14:02

answered Dec 29, 2016 at 13:47

Aleksej

23.1k6 gold badges38 silver badges41 bronze badges

Collectives™ on Stack Overflow

Oracle Regexp_replace

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related