Archives of the TeradataForum

Message Posted: Sun, 28 Jul 2002 @ 04:43:26 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi Everyone:

When I replied to the string replacement question with a technique to change 1 character per pass, I mentioned that it could be written to change 2 per pass.

Several people asked me how to do it. So, I finally had some time to test it and here it is:

/* contents before update  */
sel * from switcher;

 *** Query completed. 2 rows found. One column returned.

swcol
------------
acct-chg-it
account-chg-

/*  changes 2 "-" characters to "_" characters  */
upd switcher
from (sel col1
    ,SUBSTRING(col2 FROM 1 FOR
    POSITION('-' IN col2)-1 ) ||'_'
  ||SUBSTRING(col2 FROM POSITION('-' IN col2)+1 ) from
( sel * from ( SELECT  swcol
                ,SUBSTRING(swcol FROM 1 FOR
                   POSITION('-' IN swcol)-1 ) ||'_'
                   ||SUBSTRING(swcol FROM POSITION('-' IN swcol)+1 )
                   FROM   switcher) dt (col1, col2) ) dt2 ) dt3(cola, colb)
set swcol = colb
where cola = swcol;

/* contents after update  */
sel * from switcher;

 *** Query completed. 2 rows found. One column returned.

swcol
------------
acct_chg_it
account_chg_

Hope this gives you some ideas.

Regards,

Mike

	<Prev	Next>		<<First	<Prev	Next>	Last>>