CODEWITHSUNDEEP
oracle-databaseoracle11g
shanky
shanky

Reputation: 801

Replace characters (&amp) in oracle

I'm trying to use the inbuilt oracle function to replace '&amp' with &. I wrote two functions below but it's not working for me. On running these two in sql developer tool its asking me for input. My requirement is to replace html entities.

select REPLACE('&', '&', '&') from DUAL; select regexp_replace('&', '&', '&') from DUAL;

Could any one please tell me what's wrong I am doing?.

Upvotes: 0

Views: 3782

Answers (2)

MarcG
MarcG

Reputation: 1

You should execute set define off before executing your query to suppress processing of substitution variables which start by &.

The command set define off will stay active until you enter set define on on your sqlplus session or sql developer worksheet.

thatjeffsmith answer was right but set scan off is tagged obsolete by Oracle. Anyway, it still work.

Upvotes: 0

thatjeffsmith
thatjeffsmith

Reputation: 22427

This should probably be marked as a duplicate, but you need to add this to your script

SET SCAN OFF - that tells us to ignore the & which is used for replacing text when running code in SQLPlus

enter image description here

Once you have that disabled, you can run the queries one at a time with data grids (the first execution mode) or as sqlplus scripts (the second execution mode).

Upvotes: 2

Related Questions