Teradata Parsing Full Name field sql

I have a column with a name value with a data type of char(64) LATIN in a Teradata table. The values look like ‘SMITH JOHN J ‘, ‘Doe Jane Anne ‘, etc. The spaces between the elements vary from value to value. I am able to parse out the last name out with a left, but I am having trouble parsing out the first name and middle initial/name. I have tried using the index and position functions, but I am not getting the desired result. Has anyone encountered a similar scenario?

Answer

You could use regexp_substr() and adjust the occurence argument, which specifies the number of the occurence to return:

select 
    regexp_substr(name, 'w+', 1, 1) last_name,
    regexp_substr(name, 'w+', 1, 2) middle_name,
    regexp_substr(name, 'w+', 1, 3) first_name
from mytable

In PCRE notation, which Teradata used, w matches on word characters (alphanumeric and the underscore). You might want to make the regex a little broader with S (anything but a space).