Archives of the TeradataForum

Message Posted: Wed, 14 Oct 2015 @ 15:40:34 GMT

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

I think this will work for you. You can extract one character at a time from the outstr column using repeated substring operations (if it is of manageable size)

Explanation :

(.) match one single character

\1+ represents the matched character i.e character followed by itself 1 or more time (multiple occurence) is replaced by \1 i.e the matched character

     select instr, regexp_replace(instr,'(.)\1+','\1',1,0,'i') outstr from ( select '111122233333111'
     (char(100)) as  instr from (sel 'x' as x) tmp union select 'SENDHIL' (char(100)) as  instr
     from (sel 'x' as x) tmp union select 'SSEEENNNNDDDHIIIIILLL' (char(100)) as  instr from (sel 'x' as x) tmp

     union
     select '999877655543322221' (char(100)) as  instr from (sel 'x' as x) tmp
     union select 'SSEENNDDHHIILL    KKKUUUMMMAAARR   SSSSUUUNNNNDDDAAAARRRAMMMM'
     (char(100)) as  instr from (sel 'x' as x) tmp
     ) tmp

thanks,

Sendhil K Sundaram

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