Home Page for the TeradataForum
 

Archives of the TeradataForum

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


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


Subj:   Re: Algorithm to separate string text using spaces
 
From:   Tom Umberger

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