How to switch first word and last word in a string by oracle

Question

I need a help. Could you give me an advice for switch the first word and last word in a string by oracle

For example:

Input:

Column 1

apple is a red this

Expected output:

Column 2

this is a red apple

Thank you.

Answer 1

You could use REGEXP_REPLACE with capture groups. Capture the first and last words, along with any possible content in the middle. Then replace with the first and last words switched.

SELECT REGEXP_REPLACE(Col1, '^(\w+)(.*?)(\w+)$', '\3\2\1')
FROM yourTable;

Demo