Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


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

Posted on 2016-11-21
Medium Priority
Last Modified: 2016-11-24
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.
Question by:Pank Gurav
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 30

Accepted Solution

Pawan Kumar earned 2000 total points
ID: 41896978

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

Open in new window


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

Open in new window


Author Comment

by:Pank Gurav
ID: 41896980
I am trying this query in SQL developer, but the answer is not correct .
LVL 30

Expert Comment

by:Pawan Kumar
ID: 41896981
What query you r trying?
Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

LVL 30

Expert Comment

by:Pawan Kumar
ID: 41897264
Hi Pank Gurav,
Could you please update if this is done or not?

LVL 22

Expert Comment

by:Helena Marková
ID: 41897383
It works for me - SQL Navigator, Oracle 11g
LVL 32

Expert Comment

ID: 41897478
>>I am trying this query in SQL developer, but the answer is not correct<<
Please show the query and the incorrect answer.
LVL 32

Expert Comment

ID: 41897945
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question