SQL to split the word in Oracle with specified delimiter (with example of GL segment qualifiers)

Here we have used the delimiter as underscore _

SELECT

    value

FROM

    (

        SELECT

            ROWNUM  row_num,

            regexp_substr('First_Second_Third', '[^_]+', 1, level)  value

        FROM

            dual

        CONNECT BY

            regexp_substr('First_Second_Third', '[^_]+', 1, level) IS NOT NULL

    )

WHERE

    row_num = 2;

--Output:

Second

If you want to get particular row then you can use the below sample query:

SELECT compiled_value_attributes,
       Trim(Regexp_substr(compiled_value_attributes, '[^'
                                                     ||Chr(10)
                                                     ||']+', 1, 1)) AS summary,
       Trim(Regexp_substr(compiled_value_attributes, '[^'
                                                     ||Chr(10)
                                                     ||']+', 1, 2)) AS
       allow_posting,
       Trim(Regexp_substr(compiled_value_attributes, '[^'
                                                     ||Chr(10)
                                                     ||']+', 1, 3)) AS
       allow_budgeting,
       Trim(Regexp_substr(compiled_value_attributes, '[^'
                                                     ||Chr(10)
                                                     ||']+', 1, 4)) AS
       account_type,
       Trim(Regexp_substr(compiled_value_attributes, '[^'
                                                     ||Chr(10)
                                                     ||']+', 1, 5)) AS
       third_party_control,
       Trim(Regexp_substr(compiled_value_attributes, '[^'
                                                     ||Chr(10)
                                                     ||']+', 1, 6)) AS reconcile
       ,
       Trim(Regexp_substr(compiled_value_attributes, '[^'
                                                     ||Chr(10)
                                                     ||']+', 1, 7)) AS
       financial_category
FROM   fnd_flex_values
WHERE  flex_value_id = 168024