Example: Using Concatenation, SUBSTRING, and INDEX - Analytics Database - Teradata Vantage

A somewhat more complex construction employing concatenation, SUBSTRING, and INDEX might be more instructive. Suppose the employee table contains the following values.

   empno      name       
   ---------- -----------
   10021      Smith T
   10007      Aguilar J
   10018      Russell S
   10011      Chin M
   10019      Newman P

You can transpose the form of the names from the name column selected from the employee table and change the punctuation in the report using the following query:

   SELECT empno, 
   SUBSTRING(name FROM INDEX(name,' ')+1 FOR 1)||
'. '||
   SUBSTRING(name FROM 1 FOR INDEX(name, ' ')-1) 
   (TITLE 'Emp Name') 
   FROM employee ; 

The system returns the following report.

   empno      Emp Name       
   ---------- --------------
   10021      T. Smith 
   10007      J. Aguilar 
   10018      S. Russell 
   10011      M. Chin 
   10019      P. Newman