Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!drn.maxwell.syr.edu!news.maxwell.syr.edu!feed.news.tiscali.de!newsfeed.tiscali.ch!nntp.init7.net!not-for-mail
From: Rene Nyffenegger <rene.nyffenegger@gmx.ch>
Newsgroups: comp.databases.oracle.server
Subject: Re: Tricky SQL even possible?
Date: Mon, 8 Aug 2005 23:52:45 +0000 (UTC)
Organization: ADP Analyse, Design & Programmierung GmbH
Lines: 48
Message-ID: <dd8r8d$mj6$1@klatschtante.init7.net>
References: <1123538692.759012.272710@f14g2000cwb.googlegroups.com>
NNTP-Posting-Host: adsl-133-50.dsl.init7.net
X-Trace: klatschtante.init7.net 1123545165 23142 213.144.133.50 (8 Aug 2005 23:52:45 GMT)
X-Complaints-To: abuse@init7.net
NNTP-Posting-Date: Mon, 8 Aug 2005 23:52:45 +0000 (UTC)
User-Agent: slrn/0.9.8.1 (CYGWIN_NT-5.1)
Xref: dp-news.maxwell.syr.edu comp.databases.oracle.server:249144

On 2005-08-08, michaeljc70@hotmail.com <michaeljc70@hotmail.com> wrote:
> Basically, I've simplified down what I need from a much larger problem.
> I don't even know if this is possible.  What I need is to be able to
> create a select that will look at a VARCHAR2 field (call it text_field)
> and generate a row for every 5 characters in that column.  In other
> words, if text_field has 50 characters, I would expect to get back 10
> rows which would be the numbers 1 to 10.  If it has 100 characters, I
> would get back the numbers 1 to 20.

Try this:

create or replace type five_chars_t as table of varchar2(5);
/

create or replace function break_into_five(s in varchar2) return five_chars_t is
  ret five_chars_t := five_chars_t();
  i number := 0;
begin

  while i < length(s) loop
    ret.extend;
    ret(ret.count) := substr(s, i, 5);
    i := i+5;
  end loop;

  return ret;

end;
/

show errors

create table t (
  text_field varchar2(100)
);

insert into t values ('a');
insert into t values ('ABCDEFGHIJ');
insert into t values ('1234567890123');

select column_value from t t, table(break_into_five(t.text_field));

hth
Rene

-- 
  Rene Nyffenegger
  http://www.adp-gmbh.ch/