By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
WITH t(column1) AS
(
SELECT 'AAA Machine-1' FROM dual UNION ALL
SELECT 'BBB Machine-2' FROM dual UNION ALL
SELECT 'CCC Machine-3' FROM dual
)
SELECT INITCAP(TO_CHAR(TO_DATE(ROW_NUMBER()
OVER(ORDER BY column1) || '-01-01',
'YYYY-MM-DD'),
'YYYYSPth')) || ' Process' AS "Process",
REGEXP_SUBSTR(column1, '[^ ]+', 1, 2) AS "Machine"
FROM t
| Process | Machine |
|---|---|
| First Process | Machine-1 |
| Second Process | Machine-2 |
| Third Process | Machine-3 |