Archives of the TeradataForum

Message Posted: Mon, 15 Mar 2004 @ 20:14:51 GMT

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

To expound on what Barbara said, here is what we are doing to remove two or more spaces.

The first thing we did was set up a procedure so the code could be repeated everytime we received new data. In this particular example the looping structure is not included, however you could just repeat the Call Procedure CleanSpaces multiple times.

create procedure
cleanspaces
()
begin

-- Two spaces
Bt;
update TABLENAME
set address = trim (both from
substring(address from 1 for (position('  ' in address) -1))
||' ' ||
trim (both from substring(address from (position('  ' in address) +1))))
where position('  ' in address) > 0;
et;

end;

I hope you find this helpful.

William

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