How to create 2 new columns from name and surename included in 1 column in Teradata SQL?

Question

I have table in Teradata SQL like below:

NAME
Johnson Simon
Whats Ann
Pitt-House Mark
Grahap Michael Josh

This table in column: "NAME" contains name and surname of clients, but be aware that sometimes clients can have two-membered surname or have two names.

As a result I need:

NAME	SURNAME	NAME_2
Johnson Simon	Johnson	Simon
Whats Ann	Whats	Ann
Pitt-House Mark	Pitt-House	Mark
Grahap Michael Josh	Grahap	Michael Josh

I think, that it could be done, that we can take first value from column "NAME" to the first white space and it will be value of SURNAME column, and rest values give to NAME_2, but I do not know how to write it in Teradata SQL, or maybe you have other idea how to solve this issue ?

Answer 1

Use STRTOK

It splits tokens based on the specified list of delimiter characters and returns the nth token, where n is specified by the tokennum argument.

SELECT NAME, STRTOK(your_table.NAME, ' ', 1) as SURNAME, STRTOK(your_table.NAME, ' ', 2) as NAME_2 FROM your_table;