how to replace '&' and '()' in sql query for oracle using regex

Hi,
I have columns in my database which have & and () .I want to replace '&' with word 'and' '()' with word 'bracket'
is it possible to do in oracle using regex?

Eg. String: This is a test & temp ()

output like : This is a test and temp bracket.

thnks in advance.
Pank GuravAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Pawan KumarConnect With a Mentor Database ExpertCommented:
Try

WITH CTE AS
(
    
    SELECT 'This is a test & temp ()' a FROM DUAL
    
)
SELECT A , REPLACE(REPLACE(A,'&','AND'),'()','bracket') a1 FROM CTE

Open in new window


Output

 	A	                                        A1          
1	This is a test & temp ()  	This is a test AND temp bracket

Open in new window

0
 
Pank GuravAuthor Commented:
Hi,
I am trying this query in SQL developer, but the answer is not correct .
0
 
Pawan KumarDatabase ExpertCommented:
What query you r trying?
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Pawan KumarDatabase ExpertCommented:
Hi Pank Gurav,
Could you please update if this is done or not?

Regards,
Pawan
0
 
Helena Markováprogrammer-analystCommented:
It works for me - SQL Navigator, Oracle 11g
EE.PNG
0
 
awking00Commented:
>>I am trying this query in SQL developer, but the answer is not correct<<
Please show the query and the incorrect answer.
0
 
awking00Commented:
It may be that Oracle is interpreting the ampersand for defining a substitution variable . Try running the query after issuing the following command:
set define off
1
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.