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

Posted on 2016-11-21
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
LVL 24

Accepted Solution

Pawan Kumar earned 500 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 24

Expert Comment

by:Pawan Kumar
ID: 41896981
What query you r trying?
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

LVL 24

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server Linked Server Using Oracle OPS$ Windows Authenticated Account 7 59
T-SQL Convert to PL/SQL 23 78
query returning everything 11 91
unable to get sorting resultset 15 68
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

920 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now