Home Page for the TeradataForum
 

Archives of the TeradataForum

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


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


Subj:   Solution to Teradata String Replace within a string Function
 
From:   Michael Larkins

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>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016