Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2016-11-21
8
Medium Priority
?
187 Views
Last Modified: 2016-11-24
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.
0
Comment
Question by:Pank Gurav
8 Comments
 
LVL 35

Accepted Solution

by:
Pawan Kumar earned 2000 total points
ID: 41896978
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
 

Author Comment

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

Expert Comment

by:Pawan Kumar
ID: 41896981
What query you r trying?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 35

Expert Comment

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

Regards,
Pawan
0
 
LVL 22

Expert Comment

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

Expert Comment

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

Expert Comment

by:awking00
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
1

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
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.
Suggested Courses

916 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