|
|
|
 |
 |
Archives of the TeradataForumMessage Posted: Mon, 30 Apr 2012 @ 15:51:57 GMT
Hi Puneet, Another (cumbersome) way of doing this is - 1. Reverse the string. Please be sure to trim your data of trailing or leading spaces. 2. Locate the position of the 1st '/' in the reversed string and substring from 1 till location of 1st '/' minus 1. 3. Re-reverse the string. Here is the code (haven't tested it because our server is slow right now) -
-- Test data
create table lci_work_tbls.pre_xtraction
(col1 varchar(100))
primary index (col1);
insert into lci_work_tbls.pre_xtraction values
('/scm/edw/appl/download/daily/current/DWI_CPC_IRA_RATNG_DAILY');
-- Reversing the string to locate position of last '/'
create table lci_work_tbls.xtraction_process1 as (
sel substr(col1, 100, 1) ||
substr(col1, 99, 1) ||
substr(col1, 98, 1) ||
substr(col1, 97, 1) ||
substr(col1, 96, 1) ||
substr(col1, 95, 1) ||
substr(col1, 94, 1) ||
substr(col1, 93, 1) ||
substr(col1, 92, 1) ||
substr(col1, 91, 1) ||
substr(col1, 90, 1) ||
substr(col1, 89, 1) ||
substr(col1, 88, 1) ||
substr(col1, 87, 1) ||
substr(col1, 86, 1) ||
substr(col1, 85, 1) ||
substr(col1, 84, 1) ||
substr(col1, 83, 1) ||
substr(col1, 82, 1) ||
substr(col1, 81, 1) ||
substr(col1, 80, 1) ||
substr(col1, 79, 1) ||
substr(col1, 78, 1) ||
substr(col1, 77, 1) ||
substr(col1, 76, 1) ||
substr(col1, 75, 1) ||
substr(col1, 74, 1) ||
substr(col1, 73, 1) ||
substr(col1, 72, 1) ||
substr(col1, 71, 1) ||
substr(col1, 70, 1) ||
substr(col1, 69, 1) ||
substr(col1, 68, 1) ||
substr(col1, 67, 1) ||
substr(col1, 66, 1) ||
substr(col1, 65, 1) ||
substr(col1, 64, 1) ||
substr(col1, 63, 1) ||
substr(col1, 62, 1) ||
substr(col1, 61, 1) ||
substr(col1, 60, 1) ||
substr(col1, 59, 1) ||
substr(col1, 58, 1) ||
substr(col1, 57, 1) ||
substr(col1, 56, 1) ||
substr(col1, 55, 1) ||
substr(col1, 54, 1) ||
substr(col1, 53, 1) ||
substr(col1, 52, 1) ||
substr(col1, 51, 1) ||
substr(col1, 50, 1) ||
substr(col1, 49, 1) ||
substr(col1, 48, 1) ||
substr(col1, 47, 1) ||
substr(col1, 46, 1) ||
substr(col1, 45, 1) ||
substr(col1, 44, 1) ||
substr(col1, 43, 1) ||
substr(col1, 42, 1) ||
substr(col1, 41, 1) ||
substr(col1, 40, 1) ||
substr(col1, 39, 1) ||
substr(col1, 38, 1) ||
substr(col1, 37, 1) ||
substr(col1, 36, 1) ||
substr(col1, 35, 1) ||
substr(col1, 34, 1) ||
substr(col1, 33, 1) ||
substr(col1, 32, 1) ||
substr(col1, 31, 1) ||
substr(col1, 30, 1) ||
substr(col1, 29, 1) ||
substr(col1, 28, 1) ||
substr(col1, 27, 1) ||
substr(col1, 26, 1) ||
substr(col1, 25, 1) ||
substr(col1, 24, 1) ||
substr(col1, 23, 1) ||
substr(col1, 22, 1) ||
substr(col1, 21, 1) ||
substr(col1, 20, 1) ||
substr(col1, 19, 1) ||
substr(col1, 18, 1) ||
substr(col1, 17, 1) ||
substr(col1, 16, 1) ||
substr(col1, 15, 1) ||
substr(col1, 14, 1) ||
substr(col1, 13, 1) ||
substr(col1, 12, 1) ||
substr(col1, 11, 1) ||
substr(col1, 10, 1) ||
substr(col1, 9, 1) ||
substr(col1, 8, 1) ||
substr(col1, 7, 1) ||
substr(col1, 6, 1) ||
substr(col1, 5, 1) ||
substr(col1, 4, 1) ||
substr(col1, 3, 1) ||
substr(col1, 2, 1) ||
substr(col1, 1, 1) as col2,
substr(col2, 1, (position('/' in col2) - 1)) as col3
from lci_work_tbls.pre_xtraction
)
with data primary index (col2);
-- Reversing again to get the final value
create table lci_work_tbls.xtraction_cmplt as (
sel substr(col3, 100, 1) ||
substr(col3, 99, 1) ||
substr(col3, 98, 1) ||
substr(col3, 97, 1) ||
substr(col3, 96, 1) ||
substr(col3, 95, 1) ||
substr(col3, 94, 1) ||
substr(col3, 93, 1) ||
substr(col3, 92, 1) ||
substr(col3, 91, 1) ||
substr(col3, 90, 1) ||
substr(col3, 89, 1) ||
substr(col3, 88, 1) ||
substr(col3, 87, 1) ||
substr(col3, 86, 1) ||
substr(col3, 85, 1) ||
substr(col3, 84, 1) ||
substr(col3, 83, 1) ||
substr(col3, 82, 1) ||
substr(col3, 81, 1) ||
substr(col3, 80, 1) ||
substr(col3, 79, 1) ||
substr(col3, 78, 1) ||
substr(col3, 77, 1) ||
substr(col3, 76, 1) ||
substr(col3, 75, 1) ||
substr(col3, 74, 1) ||
substr(col3, 73, 1) ||
substr(col3, 72, 1) ||
substr(col3, 71, 1) ||
substr(col3, 70, 1) ||
substr(col3, 69, 1) ||
substr(col3, 68, 1) ||
substr(col3, 67, 1) ||
substr(col3, 66, 1) ||
substr(col3, 65, 1) ||
substr(col3, 64, 1) ||
substr(col3, 63, 1) ||
substr(col3, 62, 1) ||
substr(col3, 61, 1) ||
substr(col3, 60, 1) ||
substr(col3, 59, 1) ||
substr(col3, 58, 1) ||
substr(col3, 57, 1) ||
substr(col3, 56, 1) ||
substr(col3, 55, 1) ||
substr(col3, 54, 1) ||
substr(col3, 53, 1) ||
substr(col3, 52, 1) ||
substr(col3, 51, 1) ||
substr(col3, 50, 1) ||
substr(col3, 49, 1) ||
substr(col3, 48, 1) ||
substr(col3, 47, 1) ||
substr(col3, 46, 1) ||
substr(col3, 45, 1) ||
substr(col3, 44, 1) ||
substr(col3, 43, 1) ||
substr(col3, 42, 1) ||
substr(col3, 41, 1) ||
substr(col3, 40, 1) ||
substr(col3, 39, 1) ||
substr(col3, 38, 1) ||
substr(col3, 37, 1) ||
substr(col3, 36, 1) ||
substr(col3, 35, 1) ||
substr(col3, 34, 1) ||
substr(col3, 33, 1) ||
substr(col3, 32, 1) ||
substr(col3, 31, 1) ||
substr(col3, 30, 1) ||
substr(col3, 29, 1) ||
substr(col3, 28, 1) ||
substr(col3, 27, 1) ||
substr(col3, 26, 1) ||
substr(col3, 25, 1) ||
substr(col3, 24, 1) ||
substr(col3, 23, 1) ||
substr(col3, 22, 1) ||
substr(col3, 21, 1) ||
substr(col3, 20, 1) ||
substr(col3, 19, 1) ||
substr(col3, 18, 1) ||
substr(col3, 17, 1) ||
substr(col3, 16, 1) ||
substr(col3, 15, 1) ||
substr(col3, 14, 1) ||
substr(col3, 13, 1) ||
substr(col3, 12, 1) ||
substr(col3, 11, 1) ||
substr(col3, 10, 1) ||
substr(col3, 9, 1) ||
substr(col3, 8, 1) ||
substr(col3, 7, 1) ||
substr(col3, 6, 1) ||
substr(col3, 5, 1) ||
substr(col3, 4, 1) ||
substr(col3, 3, 1) ||
substr(col3, 2, 1) ||
substr(col3, 1, 1)) as col1
from lci_work_tbls.xtraction_process1
)
with data primary index (col1);
Thanks, Debojit.
| ||||||||||||||||||||||||||||||||||||||||||||||
| | ||||||||||||||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||||||||||||||
| Copyright 2016 - All Rights Reserved | ||||||||||||||||||||||||||||||||||||||||||||||||
| Last Modified: 15 Jun 2023 | ||||||||||||||||||||||||||||||||||||||||||||||||
