Archives of the TeradataForum

Message Posted: Mon, 04 Jun 2007 @ 11:20:57 GMT

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

If it was just a single (or a known number of ) occurrence of 2 blanks that you wanted to replace with a single blank, SUBSTRING and POSITION functions could have done the trick for you.

It's tricky if you don't know the number of occurences, you might have to use an UDF or so.

Or else can try a bit of recursion

     WITH RECURSIVE GETMSGES(ID, MSG) AS
     (
     SELECT ID, MSG FROM MYTABLE
     UNION ALL
     SELECT ID, SUBSTRING(MSG FROM 1 FOR POSITION('  ' IN MSG) )  ||
     SUBSTRING(MSG FROM POSITION('  ' IN MSG)+2 )   FROM GETMSGES
     WHERE   MSG LIKE '%  %'
     )
     SELECT ID, MSG FROM GETMSGES WHERE MSG NOT LIKE '%  %';

Joseph D'silva

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