Get Firstname and lastname from String in Teradata

Question

In Teradata how to get Firstname and lastname from a string example:Samaira Oberoi the function should return firstname as : Samaira Last name as :Oberoi also if there is no space it should return the whole set

Thanks

Answer 1

In TD14 there's a STRTOK function, it will return "SamairaOberoi" as first name and NULL as lastname if there's no space:

STRTOK(x,' ',1) AS firstname
STRTOK(x,' ',2) AS lastname

Or you use Standard SQL, returns an empty string as firstname and "SamairaOberoi" as last name if there's no space:

TRIM(SUBSTRING(x FROM 1 FOR POSITION(' ' IN x))) AS firstname
TRIM(SUBSTRING(x FROM       POSITION(' ' IN x))) AS last_name