sql - replace &

Archana.D

Hi All,

We need to prepare a script to replace the &(ampersand symbol) from the value set values and it has to be replace by 'and'

How can we do this ?

ex: Invoice & Packing List seal & signed in blue ink

Hans Steijntjes

use the function:

replace(value,'&',' and ')

With value being the name of the column.

If you are using SQL*Plus, be sure to issue SET DEFINE OFF before using replace.

So use:

SELECT replace(value,'&',' and ')

or use

UPDATE tablename

   SET value = replace(value,'&',' and ')

to change the value in the database.

Hans Steijntjes

use the function:

replace(value,'&',' and ')

With value being the name of the column.

If you are using SQL*Plus, be sure to issue SET DEFINE OFF before using replace.

So use:

SELECT replace(value,'&',' and ')

or use

UPDATE tablename

   SET value = replace(value,'&',' and ')

to change the value in the database.

BrunoVroman

Hello,

in SQL*Plus & has a special meaning...  So you have to use one of the two options:

a) SET DEFINE OFF   (to tell to Oracle to treat "&" as any standard character)

b) change it to CHR(38)

UPDATE mytable SET mycol = REPLACE( mycol, CHR(38), 'and' ) WHERE INSTR( mycol, CHR(38) ) > 0;

Best regards,

Bruno Vroman.

Archana.D

Thanks

BluShadow

I'd prefer to set define off when compiling the code, as then the code is clear in what it's doing.

Somebody reading code with chr(38) in it, isn't necessarily going to know what it's doing without having to go and check what chr(38) is.  Also, there could be some charactersets out there (I haven't checked) where chr(38) is not the "&" character (unlikely but possible)

OP,  the "&" character is used by SQL*Plus (and some other gui tools) to indicate a substitution variable.

See the community document:

As Bruno has said, "set define off" is used in SQL*Plus to turn of the detection of substitution variables.  In other tools they will have their own way of turning it off e.g. in TOAD you'd right click on your code and go to the "Substitution Variable Prompting" option to change what it does.

Archana.D

Thanks

mathguy

Hi Hans,

Your UPDATE solution is certainly correct, but please notice what Bruno did at the end of his solution. He wrote:

UPDATE [...]

WHERE instr(mycol, '&') > 0

The WHERE clause is important. It says "update only those rows where there is at least one ampersand character in the stored value." Without it, the UPDATE statement will update ALL the rows in the table - and even if a row has no ampersand in the mycol column, "updating" it (even to itself!) still results in undo/redo overhead. Oracle does not check to see if the new value is equal to the old one; it does not recognize that no "real" update has occurred. It treats such updated rows as any other updated rows.

If only a small fraction of rows contain ampersands, this trivial-looking WHERE clause may save a lot of time. Of course, the WHERE clause has its own overhead (it calls INSTR on every row), but that is much, much less than undo/redo overhead.

Cheers,      -       mathguy

Hans Steijntjes

You are totally correct. Thank you for your feedback.

Frank Kulash

Hi,

brunovroman wrote:Hello,in SQL*Plus & has a special meaning... So you have to use one of the two options:a) SET DEFINE OFF (to tell to Oracle to treat "&" as any standard character)b) change it to CHR(38)...

To be excruciatingly precise, you don't have to use either of those options.

Those may be the best options, but they are not the only options.  Others include

c) write the statement so that & can't be construed as a substitution variable marker

d) change the substitution variable marker from & to something that doesn't appear in the statement, such as ~

An example of c) is:

UPDATE  mytableSET     mycol  = REPLACE (mycol, '&', 'and')WHERE   mycol  LIKE '%&%';

Since substitution variable names can't begin with single-quotes, an ampersand immediately before a single-quote won't be construed as a substitution variable marker.

The same goes for percent signs.

BrunoVroman

Indeed Mathguy,

thank you for stressing the point, I didn't take the time to comment it myself.

It happens often that people overlook this "detail" and update too many rows (if not ALL) when only a fraction of the table needs to be updated. This causes additional locks, undo, redo... So I use to put a condition "only when necessary" in my UPDATE statements.

To be noted: with this idea, we have to be careful with NULLS if they can be present..

Example:

"first idea":

     UPDATE mytable SET myflag = 'x' WHERE mydate < ADD_MONTHS( sysdate, -3 ) AND mycity != 'Brussels';

then we think "we have probably to update only a small percentage of the many rows < 3 months old and != Brussels, let's be smart:

     UPDATE mytable SET myflag = 'x' WHERE mydate < ADD_MONTHS( sysdate, -3 ) AND mycity != 'Brussels' AND myflag != 'x';

and... Alas we don't have the expected result because "myflag" is nullable and some of the rows that should have been updated contain NULL...

we should have done for example

     UPDATE mytable SET myflag = 'x' WHERE mydate < ADD_MONTHS( sysdate, -3 ) AND mycity != 'Brussels' AND NVL( myflag, 'y' ) != 'x';

(we can do this mistake once (or never) and we learn also to be careful with NULLS :-)

(one rare situation where this kind of WHERE condition should not be used: if the UPDATE is in fact [also] a way to LOCK on purpose some rows for the length of a transaction)

Best regards,

Bruno

Frank Kulash

Hi,

Archana.D wrote:Hi All,We need to prepare a script to replace the &(ampersand symbol) from the value set values and it has to be replace by 'and'...

If you replace '&' with 'and' (3 characters), then 'r&d' will get changed into 'randd'.  You'd probably prefer 'r and d', with spaces before and after 'and'.

But if you replace '&' with ' and ' (5 characters), then 'r & d' will get changed to 'r  and  d', with 2 spaces before 'and', and 2 spaces after it.

If that's a problem, you can use REGEXP_REPLACE instead of REPLACE, like this:

UPDATE  mytableSET     mycol  = REGEXP_REPLACE ( mycol                                , ' ?& ?'                                , ' and '                                )WHERE   INSTR (mycol, '&')  > 0;

This way, if spaces already occur before and/or after the '&', you won't end up with unneeded double spaces.

Of course, REGEXP_REPLACE will be slower than plain old REPLACE.