CODEWITHSUNDEEP
sqlstringteradata
Overseer10
Overseer10

Reputation: 361

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 output such as this:

Name
Smith, John
Jane, Mary

Any tips on how to do this with Teradata SQL

I believe I solved the issue, albeit in a very poor way:

SELECT SUBSTR('SMITH, JOHN A', 0, (POSITION(' ' IN 'SMITH, JOHN A') + (POSITION(' ' IN SUBSTR('SMITH, JOHN A',(POSITION(' ' IN 'SMITH, JOHN A'))+ 1,50)))))

Upvotes: 0

Views: 10788

Answers (2)

Prasanna Chary
Prasanna Chary

Reputation: 11

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

Upvotes: 1

Rob Paller
Rob Paller

Reputation: 7786

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.)

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

  UNION ALL

  SELECT FullName
       , CASE WHEN POSITION(' ' IN Remainder) > 0
              THEN POSITION(' ' IN Remainder)
              ELSE CHARACTER_LENGTH(Remainder)
         END DelimPosition_
       , RecursionLevel + 1
       , SUBSTRING(Remainder FROM 0 FOR DelimPosition_ + 1)
       , SUBSTRING(Remainder FROM DelimPosition_ + 1)
    FROM cte
   WHERE DelimPosition_ > 1
     AND RecursionLevel < 3 -- Set max depth
)
SELECT FullName
     , CASE WHEN POSITION('&' IN Element) = 0
            THEN Element 
            ELSE NULL
       END AS LastName
  FROM cte c
 WHERE RecursionLevel > 2
 ORDER BY 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.

Upvotes: 0

Related Questions