RE: Use SQL to search for a substring anywhere in row, and then update that substring?

["Elvis Budimlic" <ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx>]

Again, REPLACE is the way to go, unless you are not at the release that
supports it.
In that case TRANSLATE will do the job for this specific purpose:

UPDATE test SET f1 = TRANSLATE(f1,' ','+')

Note that in this example all pluses were replaced with blanks (not sure if
that's what you want).

Elvis

-----Original Message-----
Subject: Re: Use SQL to search for a substring anywhere in row,and then
update that substring?

I'm curious, along these lines, can you replace a string of characters where
you don't know what specific position they are in?

I'm having an issue where the plus sign ("+") has been added to a field
across numerous records. Not sure what's happenig, it's happened multiple
times to different users.



On 8/8/06, Elvis Budimlic <ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
> REPLACE is the way to go.
> Prior to V5R3 this UPDATE would take care of single instance of 'SD' in
> the
> field:
>
> UPDATE test SET f1 = SUBSTR(f1, 1, POSITION('SD' IN f1) - 1) ||
>                     'SC'                                    ||
>                     SUBSTR(f1, POSITION('SD' IN f1) + 2)
> WHERE f1 LIKE '%SD%'
>
> You could run it multiple times to get all instances taken care of.  You'd
> know you're done when you get "Rows not found for update" message (SQLCODE
> 100).
>
> Elvis
>
> -----Original Message-----
> Subject: AW: Use SQL to search for a substring anywhere in row, and then
> update 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-----
> 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