Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, vishnudas.

Asked: March 17, 2018 - 11:44 am UTC

Last updated: March 19, 2018 - 4:47 am UTC

Version: 11.2.0.1.0

Viewed 1000+ times

You Asked

i have a table

create table demo(ord_no number(4),division varchar2(100));

value for the table is

insert into demo values(1,123456789012);

i just want to create a procedure to do the following

cut the division into 4 in length(means 1234 5678 9012) and display this iterated value with column ord_no 1 like

ORD_NO DIVISION
1 1234
1 5678
1 9012

please suggest a way to solve this..

and Connor said...

SQL> create table demo(ord_no number(4),division varchar2(100));

Table created.

SQL> insert into demo values(1,'123456789012');

1 row created.

SQL>
SQL> select *
  2  from demo d,
  3    table(cast(multiset(select substr(d.division,rownum*4-3,4) x from dual connect by level <= length(d.division)/4 ) as sys.odcinumberlist));

    ORD_NO DIVISION                                                                                             COLUMN_VALUE
---------- ---------------------------------------------------------------------------------------------------- ------------
         1 123456789012                                                                                                 1234
         1 123456789012                                                                                                 5678
         1 123456789012                                                                                                 9012



Search this site for "lateral" for how this is made easier in 12c.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, March 18, 2018 - 9:12 am UTC

Thank you :) :) :)
Connor McDonald
March 19, 2018 - 4:47 am UTC

glad we could help

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.