Home Page for the TeradataForum
 
https:

Archives of the TeradataForum

Message Posted: Mon, 30 Apr 2012 @ 15:51:57 GMT


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


Subj:   Re: Extracting a String From End
 
From:   debojit.pal

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.



     
  <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: 28 Jun 2020