Re: Tricky SQL even possible?

On 8 Aug 2005 15:04:52 -0700, 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.

First code an inline-view that will give you as much records as you need, returning rownum, and join that with every record of you table, checking if rownum * 5 is bigger than the length of you table, something like this:

select cnt, ...
from your_table,(select rownum cnt from dual group by cube(1,1,1...)) where length(text_field) > cnt * 5

Jaap. Received on Tue Aug 09 2005 - 12:45:01 CDT