sqloracle-database

How to reverse a string after tokenizing it in SQL


I need to tokenize a string and reverse it in SQL. For example if the string is, 'L3:L2:L1:L0', i need to reverse it as 'L0:L1:L2:L3'. The tokenizing could be done using a delimiter ':' and then reverse it. Please suggest a Function in SQL for the same.

Thanks in advance, Geetha


Solution

  • If possible, the best solution would be to change your data so that each value is stored in a different row.

    If that doesn't work, you can create a PL/SQL function.

    If you want a purely SQL solution, typically you'll have to split each value into multiple rows (cross join with an object table, or connect by level <= max number of items), and then re-aggregate the data using one of a dozen different methods (listagg, collect, stragg, xml, sys_connect_by_path, etc.)

    Another SQL-only way is to use regular expressions. This is probably the fastest, but it only works with up to 9 items because Oracle only supports 9 back references:

    --Get everything except the extra ':' at the end.
    select substr(string, 1, length(string) - 1) string from
    (
      select regexp_replace(
        --Add a delimter to the end so all items are the same
        'L3:L2:L1:L0'||':'
        --Non-greedy search for anything up to a : (I bet there's a better way to do this)
        ,'(.*?:)?(.*?:)?(.*?:)?(.*?:)?(.*?:)?(.*?:)?(.*?:)?(.*?:)?(.*?:)?(.*?:)?'
        --Reverse the back-references
        ,'\9\8\7\6\5\4\3\2\1') string
      from dual
    );