SQL TEXT STRINGS 1

[lairdr]

I am trying to update a database and replace existing text strings. The existing text strings have & within them.
The command I am inputting is
update fieldd0300 set supplier = 'ABBOTT AND COSTELLO' where supplier like 'A & C%';
This then prompts me for a value for C which is not what I want.
I have tried using \ as a delimiter but this does not seem to work, does anyone know what I should be using.

 

[sem]

You may use SET DEFINE to switch define character.

 

[lairdr]

Thanks for the advice but it doesn't seem to work

I used
set define
then

update fieldd0300 set supplier = 'ABBOTT \& COSTELLO' where supplier like 'A \& C%';

I got a response of O rows updated although I know there many instances of the string.

I also tried using \ before and after the & but this prompted me for a value for C

 

[carp]

You might try
SET ESCAPE '\'

Then try
UPDATE.....
WHERE supplier LIKE 'A \& C%';

 

[sem]

SET DEFINE command defines substitution character, not escape. You may redefine & or even skip any substitution charcter at all by SET DEFINE OFF.

 

[kraydan]

have you tried select replace..... check sql syntex to substitute values, then if it works as select you may wite a routine to update the table.
regards

 

[karluk]

The issue of embedded ampersands was discussed in thread186-27781. Perhaps the suggestions in that thread might help.