Aman Rathi Aman Rathi - 6 months ago 16
SQL Question

Pattern drawing using Oracle SQL

I am trying to draw two patterns and am stuck with different problems:

1)

*******************
******************
*****************
****************
***************
**************
*************
************
***********
**********
*********
********
*******
******
*****
****
***
**
*
NULL


I have written the below query to get this:

select CASE WHEN (r2-r1)!=0 THEN
rpad('*',r2-r1,'*') END
from (select rownum r1
,(max(level) over ()) as r2
from dual
connect by level<=20);


Problem i am facing(if you look at the pic) is null is coming at the end. I am not able to figure a way to get rid of that.

2) I am trying to draw the same pattern with spaces in between every star. Stuck here too..

All the help would be highly appreciable.

Answer
SQL> select rpad('*', level, '*') as c from dual connect by level <= 20 order by length(c) desc;

C
------------------------------------------------------------------------------------------------------------------------
********************
*******************
******************
*****************
****************
***************
**************
*************
************
***********
**********
*********
********
*******
******
*****
****
***
**
*

20 rows selected.

SQL> select rpad('*', 2*level-1, ' *') as c from dual connect by level <= 20 order by length(c) desc;

C
------------------------------------------------------------------------------------------------------------------------
* * * * * * * * * * * * * * * * * * * *
* * * * * * * * * * * * * * * * * * *
* * * * * * * * * * * * * * * * * *
* * * * * * * * * * * * * * * * *
* * * * * * * * * * * * * * * *
* * * * * * * * * * * * * * *
* * * * * * * * * * * * * *
* * * * * * * * * * * * *
* * * * * * * * * * * *
* * * * * * * * * * *
* * * * * * * * * *
* * * * * * * * *
* * * * * * * *
* * * * * * *
* * * * * *
* * * * *
* * * *
* * *
* *
*

20 rows selected.