how to remove multiple semicolons from this data below using pl/sql

Question

I want to remove the multiple semi colons from this query data. i use trim but its not working full.

;ghulam.nabi@yahoo.com.pk;NOCBSS@yahoo.com.pk;;;fo.n2@yahoo.com.pk;;mumtaz.akhta@yahoo.com.pk

Answer 1

There are two ways to do it:

If you know how many semicolons do you want to change: replace function.

select 
replace(';ghulam.nabi@yahoo.com.pk;NOCBSS@yahoo.com.pk;;;fo.n2@yahoo.com.pk;;mumtaz.akhta@yahoo.com.pk',';;',';') S
from dual

SQL Fiddle DEMO

If you want to change two or more semicolons: REGEXP_REPLACE function

select 
REGEXP_REPLACE(';ghulam.nabi@yahoo.com.pk;NOCBSS@yahoo.com.pk;;;fo.n2@yahoo.com.pk;;mumtaz.akhta@yahoo.com.pk','(;){2,}',';') as s
from dual

SQL Fiddle DEMO

More information

• Replace
• REGEXP_REPLACE