How to take values after the second space in Teradata SQL?

Question

I have table in Teradata SQL like below:

col1
---------
4561 ABC New York
3256 ABC Berlin
129 ABC  Milano

And I need to take only values after the second space, plus be aware that the second word is always "ABC", so I need values after "ABC". As a result I need something like below:

col1               col2
-------------------------------
4561 ABC New York | New York
3256 ABC Berlin   | Berlin
129 ABC  Milano   | Milano

How can I do that in Teradata SQL ?

Answer 1

Using a regex approach:

SELECT col1, REGEXP_REPLACE(col1, '[^ ]+ [^ ]+ ', '') AS col2
FROM yourTable;