Archives of the TeradataForum

Message Posted: Fri, 20 Feb 2004 @ 18:09:06 GMT

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

Beverly:

Try nesting the calculation of the position (index) instead of the remainder. This is less complex and allows for a nesting of three to ten words. THE SQL below returns the ten words from a sentence in a single row.

Tom Umberger
Umberger Consulting
Teradata Certified Master

----------------START TEST SQL---------------------
DROP       TABLE Test.TestWords;
CREATE SET TABLE TEst.TestWords , FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL
     (
      SomeKey INTEGER NOT NULL,
      SomeSentence VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC)
UNIQUE PRIMARY INDEX ( SomeKey );


insert into Test.TestWords
    values(1,'a sentence with five words');
insert into Test.TestWords
    values(2,'a larger sentence that contains ten small words in
total');
insert into Test.TestWords
    values(3,'just three words');

Update Test.TestWords
   Set SomeSentence = Trim(SomeSentence);

select somekey
  , substr(SomeSentence, 1, index_1-1) As Word_1
  , substr(SomeSentence, index_1+1, index_2-index_1) As Word_2
  , case when index_3 = index_2 then substr(SomeSentence, index_2+1)
         else substr(SomeSentence, index_2+1, index_3-index_2) end As Word_3
  , case when index_3 = index_2 then ''
         when index_4 = index_3 then substr(SomeSentence, index_3+1)
         else substr(SomeSentence, index_3+1, index_4-index_3) end As Word_4
  , case when index_4 = index_3 then ''
         when index_5 = index_4 then substr(SomeSentence, index_4+1)
         else substr(SomeSentence, index_4+1, index_5-index_4) end As Word_5
  , case when index_5 = index_4 then ''
         when index_6 = index_5 then substr(SomeSentence, index_5+1)
         else substr(SomeSentence, index_5+1, index_6-index_5) end As Word_6
  , case when index_6 = index_5 then ''
         when index_7 = index_6 then substr(SomeSentence, index_6+1)
         else substr(SomeSentence, index_6+1, index_7-index_6) end As Word_7
  , case when index_7 = index_6 then ''
         when index_8 = index_7 then substr(SomeSentence, index_7+1)
         else substr(SomeSentence, index_7+1, index_8-index_7) end As Word_8
  , case when index_8 = index_7 then ''
         when index_9 = index_8 then substr(SomeSentence, index_8+1)
         else substr(SomeSentence, index_8+1, index_9-index_8) end As Word_9
  , case when index_9 = index_8 then ''
         when index_10 = index_9 then substr(SomeSentence, index_9+1)
         else substr(SomeSentence, index_9+1, index_10-index_9) end As Word_10
  , index(SomeSentence,' ') as Index_1
  , index(substr(SomeSentence, index_1+1), ' ') + Index_1 As Index_2
  , index(substr(SomeSentence, index_2+1), ' ') + Index_2 As Index_3
  , index(substr(SomeSentence, index_3+1), ' ') + Index_3 As Index_4
  , index(substr(SomeSentence, index_4+1), ' ') + Index_4 As Index_5
  , index(substr(SomeSentence, index_5+1), ' ') + Index_5 As Index_6
  , index(substr(SomeSentence, index_6+1), ' ') + Index_6 As Index_7
  , index(substr(SomeSentence, index_7+1), ' ') + Index_7 As Index_8
  , index(substr(SomeSentence, index_8+1), ' ') + Index_8 As Index_9
  , index(substr(SomeSentence, index_9+1), ' ') + Index_9 As Index_10
from Test.TestWords;
-----------------END TEST SQL----------------------

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