Parsing a string into an table using oracle SQL

Question

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.

I don't think you can do this without stored procedures. There isn't any build-in Oracle function that does this as far as I know. — Wolph
– Wolph, Commented Aug 24, 2010 at 3:57
Another question: are you wanting to populate a new table with the contents of this legacy table? Also, is your request to do this without additional objects a wish or a cast-iron constraint? — APC
– APC, Commented Aug 24, 2010 at 4:24
The thing is, I'm not creating any new tables; I'm using this data as a part of the report, which is very simple itself and could be done using only plain sql without any procedural processing. So, I don't want to trash up my database with any new objects for this kind of task - I guess it's not worth it. But anyway, yes, my request is just a wish. — Kirill Leontev
– Kirill Leontev, Commented Aug 24, 2010 at 4:49
In fact, this stackoverflow.com/questions/2744649/… would be fine, but it doesnt seem to work in 9i. :( — Kirill Leontev
– Kirill Leontev, Commented Aug 24, 2010 at 5:05

Jon Heller · Accepted Answer · 2010-08-24 06:04:35Z

5

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;

answered Aug 24, 2010 at 6:04

Jon Heller

37.1k6 gold badges84 silver badges150 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

APC Over a year ago

+1 - this does the job without the need for any additional objects.

JulesLt Over a year ago

Worth explaining that "select level element_number from dual connect by level <= 5" basically generates a list of integers from thin air. It's a good cheat for generating sequential data where you have no source table, and can obviously we fed into other functions.

Kirill Leontev Over a year ago

Subquery in this connect by level clause doesn't work, but nevertheless, when I replace it by constant - it does fine. That would work for me, thanks.

APC Over a year ago

@BeHereNow - this use of the CONNECT BY syntax (without a PRIOR clause) was controversial in 9i, as it wasn't documented and didn't work exactly the same in all versions of the database (sometimes we had to wrap things in extra brackets). But Jon's code worked without a hitch in my 11g database.

JulesLt Over a year ago

It seems 'Kyte approved' (i.e. even though undocumented, it has been used in a few AskTom answers - I doubt Oracle would change/remove the behavior by now).

|

Wolph · Accepted Answer · 2010-08-24 03:58:45Z

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;

Hari Menon · Accepted Answer · 2010-08-24 04:05:01Z

0

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...

answered Aug 24, 2010 at 4:05

Hari Menon

35.7k15 gold badges90 silver badges111 bronze badges

4 Comments

Kirill Leontev Over a year ago

It works if I have not more than two pieces of data in my line, but I can have three or more, so I guess it's no way to write N unions each time. :)

Hari Menon Over a year ago

oh.. so you can also have <345><23><87> ??

Kirill Leontev Over a year ago

Yes, I can, and I have no information about how many pieces are there in each line. All I know there are, say, no less than one, and at most six or seven of them in each line.

JulesLt Over a year ago

See Jon Earle's answer above - you can take a similar approach by doing a cartesian join against a fake table that returns N rows, where N is the count of data items, and then using the count to drive the offset of the INSTR.

Collectives™ on Stack Overflow

Parsing a string into an table using oracle SQL

3 Answers 3

6 Comments

Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related