Archives of the TeradataForum

Message Posted: Wed, 14 Oct 2015 @ 16:11:49 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)

Or you can create a view on top of that column with multiple substring function to extract each char.

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>>