OraFAQ Forum: SQL & PL/SQL » Replace every even number occurance of comma with # in a String

Home » SQL & PL/SQL » SQL & PL/SQL » Replace every even number occurance of comma with # in a String (Oracle 11g R2)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Replace every even number occurance of comma with # in a String [message #625914]

Thu, 16 October 2014 02:49

javed.khan
Messages: 340
Registered: November 2006
Location: Banglore

Senior Member

Gurus,
Hope everythings good at your side.

I have one requirement
I need to replace every EVEN number of occurrence of comma with '#' in a string
For example

If the string is

Abraham, Sibi M.,Abramenkova, Anna,Abreu, Maria Helena,Acevedo, Liliana,Addy, Ibironke

The out put will be

Abraham, Sibi M.#Abramenkova, Anna#Abreu, Maria Helena#Acevedo, Liliana#Addy, Ibironke

So here as we can see that the first comma is retained , the second occurrences is replaced with # 3rd is retained and 4rth occurrences is replaced with # and so on.

Thanks & Regards
Javed A. Khan

Report message to a moderator

Re: Replace every even number occurance of comma with # in a String [message #625918 is a reply to message #625914]

Thu, 16 October 2014 03:30

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Are you sure you need the RULE of every even occurrence? Looking at your sample data it seems, all such occurrences will always have the comma together with other characters before and after.

Like, 'Helena,Acevedo' has 'a,A' together as an expression to be replaced by 'a#A'. Unlike, 'Anna,Abreu, Maria Helena,Acevedo' where the other concurrence of comma has a space immediately after it.

Report message to a moderator

Re: Replace every even number occurance of comma with # in a String [message #625919 is a reply to message #625918]

Thu, 16 October 2014 03:40

javed.khan
Messages: 340
Registered: November 2006
Location: Banglore

Senior Member

Lalit,

Thanks for looking into this.. yes the string will be a mix of Alphabets , spaces and '.' mostly but the rule is to create # on every even number of ','.

Thanks & Regards
Javed A. Khan

Report message to a moderator

Re: Replace every even number occurance of comma with # in a String [message #625920 is a reply to message #625919]

Thu, 16 October 2014 03:48

javed.khan
Messages: 340
Registered: November 2006
Location: Banglore

Senior Member

The Spaces can be eliminated , I will be handle them in a different way...

Report message to a moderator

Re: Replace every even number occurance of comma with # in a String [message #625927 is a reply to message #625914]

Thu, 16 October 2014 05:19

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

SQL> with data as (
  2    select 'Abraham, Sibi M.,Abramenkova, Anna,Abreu, Maria Helena,Acevedo, Liliana,Addy, Ibironke' val from dual
  3  )
  4  select regexp_replace(val,'([^,]*,[^,]*),','\1#') from data
  5  /
REGEXP_REPLACE(VAL,'([^,]*,[^,]*),','\1#')
--------------------------------------------------------------------------------------
Abraham, Sibi M.#Abramenkova, Anna#Abreu, Maria Helena#Acevedo, Liliana#Addy, Ibironke

Report message to a moderator

Re: Replace every even number occurance of comma with # in a String [message #625928 is a reply to message #625927]

Thu, 16 October 2014 05:24

javed.khan
Messages: 340
Registered: November 2006
Location: Banglore

Senior Member

Wow.. Simply Classic Michel.
Thanks Alot , you just saved my day.
This was dependent for 30 more activities i had.

Superb as always.

Thanks a ton.

Javed A. Khan

Report message to a moderator

Previous Topic:	Merge collections ?
Next Topic:	Need a hand finding the script

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 09:42:01 CDT 2024