Home Page for the TeradataForum
 

Archives of the TeradataForum

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


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


Subj:   Re: To remove Two continuos Blanks from Data
 
From:   D silva, Joseph Vinish

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