Archives of the TeradataForum

Message Posted: Tue, 25 Sep 2007 @ 19:32:04 GMT

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

<-- Anonymously Posted: Tue, 25 Sep 2007 14:51:13 -->

In the last response, it losts CR/LF, and messed up.

Here is another one. Hope this would work.

     create volatile table nametable
     (c1 VARCHAR(42))
     primary index (c1)
     on commit preserve rows;


     insert into nametable sel 'Scott S Smith';
     insert into nametable sel 'Mike K. Carter';
     insert into nametable sel 'Sam Simpson';


     SELECT CASE
     WHEN INDEX(SUBSTR(c1, INDEX(c1, ' ')+1, CHAR(c1)),' ') <= 0 THEN
     SUBSTR(c1, INDEX(c1, ' ')+1, CHAR(c1)) || ', ' || SUBSTR(c1,0,INDEX(c1,
     ' '))
     ELSE
     SUBSTR(c1, INDEX(SUBSTR(c1, INDEX(c1, ' ')+1, CHAR(c1)),' ')+INDEX(c1,'
     ')+1,CHAR(c1)) ||', ' ||
     SUBSTR(c1, 0, INDEX(c1, ' ')+INDEX(SUBSTR(c1, INDEX(c1, ' ')+1,
     CHAR(c1)),' ') )
     END
     FROM NAMETABLE;

     < CASE  expression >
     Smith, Scott S
     Simpson, Sam
     Carter, Mike K.

     select * from dbc.dbcinfo;
     InfoKey InfoData
     RELEASE V2R.05.01.02.39
     VERSION 05.01.02.64

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