Recursively build a string

[Lash20]

Hi all, How do you recursively remove the last character from a string in Oracle e.g for string 'HPN12345', I would like to build the following: HPN12345 HPN1234 HPN123 HPN12 HPN1 HPN I know how to do this in Oracle 11 using a recursive common table expression (CTE), but I need to implement this in Oracle 10g (without using a user function). Thanks

[jschell]

A while loop based on the length would work.

[Niral Soni]

Try this one... Change value of MIN_DEPTH and CHAR_STR as required.

SELECT SUBSTR(A.CHAR_STR, 0, LENGTH(A.CHAR_STR) - LEVEL + 1) OUTPUT
FROM (SELECT 'HPN12345' CHAR_STR, 3 MIN_DEPTH FROM DUAL) A
CONNECT BY LENGTH(A.CHAR_STR) - LEVEL + 1 >= A.MIN_DEPTH;

Thanks & Regards, Niral Soni

[Lash20]

Thanks Niral.. Your code was very helpful. It helped point me in the right direction. Much appreciated!