TERADATA - How to split a character column and keep the last token?

Question

I have a table with article names and I would like to select the last word of each article of the table.

Right now I'm doing it in SAS and I my code looks like:

PROC SQL;
    CREATE TABLE last_word as
    SELECT scan(names,-1) as last_w
    FROM articles;
QUIT;

I am aware of the STRTOK function in TERADATA but it seems that it only accepts positive values as indexes and in my case the articles names don't have a constant number of words.

Answer 1

You could use function REGEXP_SUBSTR to do this:

CREATE TABLE last_word as
SELECT REGEXP_SUBSTR(names, '[^,]+$') as last_w
FROM articles;

The Regex here will grab the last element of the list, where the list is comma delimited.