AW: Use SQL to search for a substring anywhere in row, and then u pdate that substring?

Hi,

if you are on release V5R3 or higher, you may use the SQL Scalar function
REPLACE

update test
   set MyText = Replace(MyText, 'SC', 'SD');

Birgitta

-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Dan
Gesendet: Dienstag, 8. August 2006 18:29
An: Midrange Systems Technical Discussion
Betreff: Use SQL to search for a substring anywhere in row, and then update
that substring?

I'm almost certain this can't be done, but I've thought that before on other
SQL questions and have been consistently proven wrong.

Can I use SQL to find a substring of 2 characters anywhere in a given field,
then replace those 2 characters with 2 new characters?

For example:
Combination Notice - SC - 42
EFT Schedule - SC - 19
Courtesy Letter - SC - 41

I would like to search for 'SC' in these rows, and replace any occurrences
of it with 'SD'.  The search string "should" exist no more than once in each
row.

TIA,
Dan