Archives of the TeradataForum
Message Posted: Sun, 28 Jul 2002 @ 04:43:26 GMT
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.
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|