Re: Tricky SQL even possible?

On 2005-08-08, michaeljc70_at_hotmail.com <michaeljc70_at_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/