Archives of the TeradataForum
Message Posted: Wed, 21 Sep 2005 @ 19:54:50 GMT
| 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
| 
