How to reverse a string after tokenizing it in SQL

Question

Hi,

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

Answer 1

Something like :

SELECT
  REGEXP_REPLACE('L1:L2:L3',
                 '([[:alnum:]]{1,}):([[:alnum:]]{1,}):([[:alnum:]]{1,})',
                 '\3 \2 \1') "REGEXP_REPLACE"
from dual

But you might need to detail what constitutes a token.

Answer 2

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
);

Answer 3

Here is a solution using a PL/SQL pipelined function to split the elements:

create type t_str_array as table of varchar2(4000);

create or replace function split_str (p_str in varchar2,
                                      p_separator in varchar2 := ':') return t_str_array pipelined
as
  l_str varchar2(32000) := p_str || p_separator;
  l_pos pls_integer;
begin

  loop
    l_pos := instr(l_str, p_separator);
    exit when (nvl(l_pos,0) = 0);
    pipe row (ltrim(rtrim(substr(l_str,1,l_pos-1))));
    l_str := substr(l_str, l_pos+1);
  end loop;

  return;

end split_str;

Then you would use normal SQL to order the elements:

select * from table(split_str('L3:L2:L1:L0')) order by column_value

Answer 4

declare
  s varchar2(1000) := 'L 1 0:L9:L8:L7:L6:L5:L4:L3:L2:L1:L0';
  j number := length(s);
begin
  for i in reverse 1..length(s) loop
    if substr(s, i, 1) = ':' then
      dbms_output.put(substr(s, i + 1, j - i) || ':');
      j := i - 1;
    end if;
  end loop;    
  dbms_output.put_line(substr(s, 1, j));
end;

Answer 5

Since you use Oracle it would be easy to generate a java stored procedure passing the string and then

1. split sting into array
2. loop array backwards and concate the resulting string
3. return the resulting string

this will be a small java code and not slower then pl/sql. but if you want to use pl/sql you can possibly also use DBMS_UTILITY.table_to_comma/.comma_to_table. But as the function name let assume -> you have to use "," as token.