Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Wed, 21 Sep 2005 @ 19:54:50 GMT


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


Subj:   Re: Query to get the last space in a string.
 
From:   sanjaya.nagabhushan

You are better off trying a UDF or a TDSP but the query below does the job. It assumes that your string only has alphabets and numbers based on the example that you provided. In case there are other characters, You can add additional trim clauses.

Simply put, I am trimming all the trailing non-blank characters & then taking the number of characters in the resulting string to get the position of the last blank character.

     sel
     characters
     (
     trim (trailing '0' from
     trim (trailing '1' from
     trim (trailing '2' from
     trim (trailing '3' from
     trim (trailing '4' from
     trim (trailing '5' from
     trim (trailing '6' from
     trim (trailing '7' from
     trim (trailing '8' from
     trim (trailing '9' from
     trim (trailing 'a' from
     trim (trailing 'b' from
     trim (trailing 'c' from
     trim (trailing 'd' from
     trim (trailing 'e' from
     trim (trailing 'f' from
     trim (trailing 'g' from
     trim (trailing 'h' from
     trim (trailing 'i' from
     trim (trailing 'j' from
     trim (trailing 'k' from
     trim (trailing 'l' from
     trim (trailing 'm' from
     trim (trailing 'n' from
     trim (trailing 'o' from
     trim (trailing 'P' from
     trim (trailing 'q' from
     trim (trailing 'r' from
     trim (trailing 's' from
     trim (trailing 't' from
     trim (trailing 'u' from
     trim (trailing 'v' from
     trim (trailing 'w' from
     trim (trailing 'x' from
     trim (trailing 'y' from
     trim (trailing 'z' from
     trim (trailing 'A' from
     trim (trailing 'B' from
     trim (trailing 'C' from
     trim (trailing 'D' from
     trim (trailing 'E' from
     trim (trailing 'F' from
     trim (trailing 'G' from
     trim (trailing 'H' from
     trim (trailing 'I' from
     trim (trailing 'J' from
     trim (trailing 'K' from
     trim (trailing 'L' from
     trim (trailing 'M' from
     trim (trailing 'N' from
     trim (trailing 'O' from
     trim (trailing 'P' from
     trim (trailing 'Q' from
     trim (trailing 'R' from
     trim (trailing 'S' from
     trim (trailing 'T' from
     trim (trailing 'U' from
     trim (trailing 'V' from
     trim (trailing 'W' from
     trim (trailing 'X' from
     trim (trailing 'Y' from
     trim (trailing 'Z' from
     'CENTURY PREFERRED OVER 65 OOA RX'
     ))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))
     ) as Pos;

Regards,

Sanjaya



     
  <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: 15 Jun 2023