SELECT ROWNUM,
       TRIM(column_value) ref_string
FROM   XMLTABLE(('"'||REPLACE('AAA,BBB,CCC',',','","')||'"')) 
;

-- To get data in a specific order ROW_NUMBER() can be used

SELECT  row_num, ref_string
FROM (SELECT
       ref_string,
       ROW_NUMBER() OVER (PARTITION BY 1 ORDER BY 1 ASC) as row_num
      FROM (SELECT TRIM(column_value) ref_string
            FROM   XMLTABLE(('"'||REPLACE('AAA,BBB,CCC',',','","')||'"')) 
           )
     )
;

-- To get specific element in the output - first element [AAA]

SELECT  ref_string
FROM (SELECT
       ref_string
       ,ROW_NUMBER() OVER (PARTITION BY 1 ORDER BY 1 ASC) as row_num
      FROM (SELECT TRIM(column_value) ref_string
            FROM   XMLTABLE(('"'||REPLACE('AAA,BBB,CCC',',','","')||'"'))
           )
     )
WHERE row_num =1
;