Explanation of the query

teradata

Can somebody explain me the below query?

select SUBSTR(SUBSTR(PART_SEQ_NBR, 
                     POSITION('_' in PART_SEQ_NBR )+1, 
                     char_length(PART_SEQ_NBR) 
                    ),
              1,
              POSITION('_' in SUBSTR(PART_SEQ_NBR, 
                                     POSITION('_' in PART_SEQ_NBR )+1, 
                                     char_length(PART_SEQ_NBR) 
                                    )
                      ) -1
             ) AS PRD_NAM   
from PRD_MWE_GPRSDATPART_PERM_S

Thank you in advance

Best Answer

After formatting the query and especially the expression, it's pretty obvious that the expression can be split/rewqitten in 2 parts:

select SUBSTR( string, 
               1, 
               POSITION('_' in string) - 1 
             )   AS PRD_NAM   
from PRD_MWE_GPRSDATPART_PERM_S

where string is:

       SUBSTR( PART_SEQ_NBR, 
               POSITION('_' in PART_SEQ_NBR ) + 1, 
               CHAR_LEGTH(PART_SEQ_NBR) 
             )

The 2 expressions - the main and the (string) sub-expression - are very similar as well. If it's still not obvious what they do, one has only to try the query, putting a few underscore _ characters in the data of the column.