Parsing a string into an table using oracle SQL

Question

Hi, guys. Say I have the following table:

ID | String
---+---------  
1  | <123><345>  
2  | <1-2><45-67>  
3  | <345-321><234>

This is a legacy data format in my app which is currently impossible to avoid. What I need to acheive, is:

ID | String
---+---------
1  | <123>  
1  | <345>  
2  | <1-2>  
2  | <45-67>  
3  | <345-321>  
3  | <234>  

Any suggestions about how to acheive this result using only plain Oracle SQL without creating any additional objects or pl-sql procedures?

Thanks.

Answer 1

If you consider using stored procedures anyway, try this:

Source: http://jasonvogel.blogspot.com/2006/11/oracle-sql-converting-one-row-into-two.html

CREATE OR REPLACE FUNCTION split (
 s_delimited_list_in     VARCHAR2,
 s_delimiter_in          VARCHAR2 := ',')
RETURN prod_types.type_string_array PIPELINED
IS
/*
@Usage Example:
select * from table(split('one,two,three'));
*/
 l_idx               PLS_INTEGER;
 l_list              VARCHAR2(32767) := s_delimited_list_in;
 l_value             VARCHAR2(32767);
 ls_delimiter        VARCHAR2(100) := NVL(s_delimiter_in,',');
BEGIN
LOOP
 l_idx := INSTR(l_list,ls_delimiter);

 IF (l_idx > 0) THEN

    PIPE ROW(SUBSTR(l_list,1,l_idx-1));
    l_list := SUBSTR(l_list,l_idx+LENGTH(ls_delimiter));

 ELSE

    PIPE ROW(l_list);
    EXIT;

 END IF;
END LOOP;

RETURN;

END SPLIT;

Answer 2

Try this:

SELECT Id, SUBSTR(String,1,INSTR(String,'>',1,1)) FROM MyTable
UNION ALL
SELECT Id, SUBSTR(String,INSTR(String,'<',1,2)) FROM MyTable

I am an MS SQL Server user so I am not sure if it'll work but let me know...

Answer 3

select id, string
  ,substr(string, instr(string, '<', 1, element_number)
    ,instr(string, '>', 1, element_number) - instr(string, '<', 1, element_number) + 1) result
from test
cross join
(
  select level element_number from dual connect by level <=
    (select max(length(string) - length(replace(string, '<', null))) max_elements from test)
) extra_rows
where element_number <= length(string) - length(replace(string, '<', null))
order by id, element_number;