Teradata String Manipulation (second Space)

I'm having great difficulty solving this seemingly easy task: Purpose: Create a query that eliminates the middle Initial Example Name Smith, John A Jane, Mary S I would like an ou

Solution 1:

select a,
substr(a,1,index(a,' '))|| substr(trim(substr(a,index(a,' '))),1,index(trim(substr(a,index(a,' '))),' ')),
substr(trim(substr(a,index(a,' '))),index(trim(substr(a,index(a,' '))),' ')) last_name
from a

Solution 2:

The challenge is making sure your names are consistently formatted. (Last_Name, Given_Name Middle_Initial) If they are then you may be able to solve this with recursive SQL. The following SQL would take Given_Name Last_Name and return Last_Name. You may be able to tweak it to accomplish your specific task. (My sample data was not consistently formatted so I was stuck trying to find the second (or third) occurrence of a white space character.)

WITHRECURSIVE cte (FullName, DelimPosition, RecursionLevel, Element, Remainder) AS
(
  SELECT FullName
       , 0AS DelimPosition_
       , 0 
       , CAST(''ASVARCHAR(128))
       , FullName
    FROM MyDatabase.Persons

  UNIONALLSELECT FullName
       , CASEWHENPOSITION(' 'IN Remainder) >0THENPOSITION(' 'IN Remainder)
              ELSECHARACTER_LENGTH(Remainder)
         END DelimPosition_
       , RecursionLevel +1
       , SUBSTRING(Remainder FROM0FOR DelimPosition_ +1)
       , SUBSTRING(Remainder FROM DelimPosition_ +1)
    FROM cte
   WHERE DelimPosition_ >1AND RecursionLevel <3-- Set max depth
)
SELECT FullName
     , CASEWHENPOSITION('&'IN Element) =0THEN Element 
            ELSENULLENDAS LastName
  FROM cte c
 WHERE RecursionLevel >2ORDERBY FullName;

Another option would be to implement a UDF that returns the rightmost n characters of a string. (e.g.RIGHT(FullName, n))

If the formatting is not consistent then we have to look at other less graceful options.

Hope this helps.