Home Page for the TeradataForum
 
https:

Archives of the TeradataForum

Message Posted: Tue, 01 May 2012 @ 15:57:22 GMT


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


Subj:   Re: Extracting a String From End
 
From:   De Pons, Paul

Although the recursive works and is probably more efficient here is another alternative using a product join checking the last 50 characters for a '/'.

Here the SQL is a bit more concise but requires the string have a '/' and a file name larger than 50 char. Of course that could be adjusted.

     SELECT  STRING_TXT
                     ,SUBSTR(STRING_TXT, (CHAR(STRING_TXT) - DAY_OF_CALENDAR + 1)
      ) FILE_NAME
       FROM ( SELECT
     '/SCM/EDW/APPL/DOWNLOAD/DAILY/CURRENT/DWI.CPC.IRA.RATNG.DAILY' AS
     STRING_TXT) X
     CROSS JOIN
                   SYS_CALENDAR.CALENDAR a
     WHERE DAY_OF_CALENDAR BETWEEN 1 AND 50
           AND  POSITION('/' IN SUBSTR(STRING_TXT, (CHAR(STRING_TXT) -
     DAY_OF_CALENDAR)  ) ) = 1
     QUALIFY  ROW_NUMBER()  OVER ( PARTITION BY STRING_TXT
                                             ORDER BY DAY_OF_CALENDAR )  = 1

Paul De Pons
Architect
JM Service Center, LLC.



     
  <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