1 2 3 Previous Next 32 Replies Latest reply: Sep 30, 2009 9:23 AM by cd_2 Go to original post RSS
      • 15. Re: Draw SQUARE using SQL
        21205
        what's confusing me, is: where do the spaces come from?...

        never mind,... now I see where the spaces come from...

        +...time for a break, a cup of coffee+
        • 16. Re: Draw SQUARE using SQL
          BluShadow
          PrazY wrote:
          Oops... Triangle....

          can some one simplify this :(
          Not sure if it can be simplified really.
          It can be made more complex...
          SQL> ed
          Wrote file afiedt.buf
          
            1  select decode(rownum, :sz, replace(line,' ','*'),
            2         regexp_replace(
            3           regexp_replace(line,'(^ {'||to_char(floor((:sz*2)/2)-rownum,'fm99')||'}) ','\1*'),
            4           ' ( {'||to_char(floor((:sz*2)/2)-(rownum),'fm99')||'})$', '*\1')
            5               )
            6         as triangle
            7  from
            8    (select rpad(' ',((:sz)*2)-1,' ') as line
            9    from dual
           10    connect by rownum <= :sz
           11*   )
          SQL> /
          
          TRIANGLE
          ------------------------------------------------------------------------------------------------
                    *
                   * *
                  *   *
                 *     *
                *       *
               *         *
              *           *
             *             *
            *               *
           *                 *
          *********************
          
          11 rows selected.
          
          SQL>
          Or done differently...
          SQL> ed
          Wrote file afiedt.buf
          
            1  select lpad(' ',(floor((:sz*2)-1)/2)-(rownum-1),' ')||'*'||
            2         lpad(decode(rownum,:sz,'*',' '),((rownum-1)*2)-1,decode(rownum,:sz,'*',' '))||
            3         decode(rownum,1,null,'*') as line
            4  from dual
            5* connect by rownum <= :sz
          SQL> /
          
          LINE
          -------------------------------------------------------------------------------------------
                    *
                   * *
                  *   *
                 *     *
                *       *
               *         *
              *           *
             *             *
            *               *
           *                 *
          *********************
          
          11 rows selected.
          
          SQL> exec :sz := 5;
          
          PL/SQL procedure successfully completed.
          
          SQL> /
          
          LINE
          -------------------------------------------------------------------------------------------
              *
             * *
            *   *
           *     *
          *********
          
          SQL> exec :sz := 13;
          
          PL/SQL procedure successfully completed.
          
          SQL> /
          
          LINE
          -------------------------------------------------------------------------------------------
                      *
                     * *
                    *   *
                   *     *
                  *       *
                 *         *
                *           *
               *             *
              *               *
             *                 *
            *                   *
           *                     *
          *************************
          
          13 rows selected.
          ... but simplified.... hmmmm.... tricky.
          • 17. Re: Draw SQUARE using SQL
            BluShadow
            BluShadow wrote:
            ... but simplified.... hmmmm.... tricky.
            This is the best I can manage so far...
            SQL> ed
            Wrote file afiedt.buf
            
              1  select regexp_replace(lpad(' ',:sz-rownum,' ')||
              2                        rpad('-',(rownum-1)*2,decode(rownum,:sz,'*',' '))||'-'
              3                       ,'(^| )-|-$'
              4                       ,'\1*') as triangle
              5  from dual
              6* connect by rownum <= :sz
            SQL> /
            
            TRIANGLE
            ----------------------------------------------------------------------------------
                  *
                 * *
                *   *
               *     *
              *       *
             *         *
            *************
            
            7 rows selected.
            
            SQL> exec :sz := 5;
            
            PL/SQL procedure successfully completed.
            
            SQL> /
            
            TRIANGLE
            ----------------------------------------------------------------------------------
                *
               * *
              *   *
             *     *
            *********
            
            SQL> exec :sz := 11;
            
            PL/SQL procedure successfully completed.
            
            SQL> /
            
            TRIANGLE
            ----------------------------------------------------------------------------------
                      *
                     * *
                    *   *
                   *     *
                  *       *
                 *         *
                *           *
               *             *
              *               *
             *                 *
            *********************
            
            11 rows selected.
            
            SQL>
            • 18. Re: Draw SQUARE using SQL
              Prazy
              Great Blu... need to walk thru your query.. quite complicated!! ;)
              • 19. Re: Draw SQUARE using SQL
                BluShadow
                Doh! silly me... even simpler...
                SQL> ed
                Wrote file afiedt.buf
                
                  1  select lpad(' ',:sz-rownum,' ')||rpad('*',(rownum-1)*2,decode(rownum,:sz,'*',' '))||'*' as triangle
                  2  from dual
                  3* connect by rownum <= :sz
                SQL> /
                
                TRIANGLE
                ----------------------------------------------------------------------------------------------------------
                          *
                         * *
                        *   *
                       *     *
                      *       *
                     *         *
                    *           *
                   *             *
                  *               *
                 *                 *
                *********************
                
                11 rows selected.
                
                SQL> exec :sz := 7;
                
                PL/SQL procedure successfully completed.
                
                SQL> /
                
                TRIANGLE
                ----------------------------------------------------------------------------------------------------------
                      *
                     * *
                    *   *
                   *     *
                  *       *
                 *         *
                *************
                
                7 rows selected.
                
                SQL>
                • 20. Re: Draw SQUARE using SQL
                  Prazy
                  BluShadow wrote:

                  SQL> exec :sz := 7;

                  PL/SQL procedure successfully completed.

                  SQL> /

                  TRIANGLE
                  ----------------------------------------------------------------------------------------------------------
                  *
                  * *
                  * *
                  * *
                  * *
                  * *
                  *************

                  7 rows selected.

                  SQL>

                  Excellent, but your query retrieve 7 rows based on the bind variable input, but I would like to show the base line * to the count of bind variable.. i.e. the base of the triangle should have 7 asterix ******* instead or retrieving 7 line with base line of 13 asterix when you pass the bind variable as 7... :P
                  • 21. Re: Draw SQUARE using SQL
                    BluShadow
                    PrazY wrote:
                    BluShadow wrote:

                    SQL> exec :sz := 7;

                    PL/SQL procedure successfully completed.

                    SQL> /

                    TRIANGLE
                    ----------------------------------------------------------------------------------------------------------
                    *
                    * *
                    * *
                    * *
                    * *
                    * *
                    *************

                    7 rows selected.

                    SQL>

                    Excellent, but your query retrieve 7 rows based on the bind variable input, but I would like to show the base line * to the count of bind variable.. i.e. the base of the triangle should have 7 asterix ******* instead or retrieving 7 line with base line of 13 asterix when you pass the bind variable as 7... :P
                    I was basing it on the OP's post regarding squares, where the requirement was to double the width to make it proportional on the screen. :P
                    • 22. Re: Draw SQUARE using SQL
                      BluShadow
                      SQL> exec :sz := 7;
                      
                      PL/SQL procedure successfully completed.
                      
                      SQL> with t as (select ceil(:sz/2) as sz from dual)
                        2  select lpad(' ',sz-rownum,' ')||rpad('*',(rownum-1)*2,decode(rownum,sz,'*',' '))||'*' as triangle
                        3  from t
                        4  connect by rownum <= sz
                        5  /
                      
                      TRIANGLE
                      -------------------------------------------------------------------------------------------------------
                         *
                        * *
                       *   *
                      *******
                      
                      SQL> exec :sz := 15;
                      
                      PL/SQL procedure successfully completed.
                      
                      SQL> /
                      
                      TRIANGLE
                      -------------------------------------------------------------------------------------------------------
                             *
                            * *
                           *   *
                          *     *
                         *       *
                        *         *
                       *           *
                      ***************
                      
                      8 rows selected.
                      
                      SQL>
                      Edited by: BluShadow on Sep 10, 2009 1:21 PM
                      slight correction
                      • 23. Re: Draw SQUARE using SQL
                        94799
                        http://thedailywtf.com/Articles/Stupid-Coding-Tricks-The-TSQL-Madlebrot.aspx
                        • 24. Re: Draw SQUARE using SQL
                          BluShadow
                          padders wrote:
                          http://thedailywtf.com/Articles/Stupid-Coding-Tricks-The-TSQL-Madlebrot.aspx
                          Yeah, I've seen that one before. Now that IS crazy code. :D
                          • 25. Re: Draw SQUARE using SQL
                            666352
                            Hi Rob,

                            Or
                            SQL> var sz number;
                            SQL> exec :sz:=7;
                            
                            Procédure PL/SQL terminée avec succès.
                            
                            SQL> select s square
                              2  from dual
                              3  model
                              4  dimension by (1 i)
                              5  measures (cast(null as varchar2(100)) s)
                              6  rules
                              7  (s[for i from 1 to :sz increment 1] = case when cv(i) in (1,:sz) then 
                              8                                             replace(rpad(' ',2*:sz),' ','*')
                              9                                         else  '*'|| rpad (' ',2*:sz - 2) ||'*' end
                             10  );
                            
                            SQUARE
                            --------------------------------------------------------------------------------
                            **************
                            *            *
                            *            *
                            *            *
                            *            *
                            *            *
                            **************
                            
                            7 ligne(s) sélectionnée(s).
                            
                            SQL> 
                            • 26. Re: Draw SQUARE using SQL
                              Hoek
                              :D

                              I just stumbled on this question, but what a great thread this is, thanks to all contributors!
                              "T-SQL to generate... Mandelbrot."
                              That reminds me of the fact that here on OTN, Salim did it in SQL, and it still amazes me:

                              Re: graph problem
                              one of my favorite threads, and I'm going to add this one as well

                              edit

                              By the way: Salim, when can we expect a MODEL-clause that generates a smiley?
                              I have this strange requirement from a customer, you see... ;)

                              Edited by: hoek on Sep 10, 2009 6:14 PM
                              • 27. Re: Draw SQUARE using SQL
                                666352
                                Hi Hoek,

                                Thanks, i like model clause :).

                                Regards Salim.
                                • 28. Re: Draw SQUARE using SQL
                                  Prazy
                                  BluShadow wrote:
                                  SQL> exec :sz := 7;
                                  
                                  PL/SQL procedure successfully completed.
                                  
                                  SQL> with t as (select ceil(:sz/2) as sz from dual)
                                  2  select lpad(' ',sz-rownum,' ')||rpad('*',(rownum-1)*2,decode(rownum,sz,'*',' '))||'*' as triangle
                                  3  from t
                                  4  connect by rownum <= sz
                                  5  /
                                  You did it Blu, gee thanks! I should learn how to simplify queries... And as Hoek said, it was fun watching and playing with this thread.... ;).. should do it often...
                                  • 29. Re: Draw SQUARE using SQL
                                    babaravi
                                    hi,
                                    right angle triangle.
                                    
                                    
                                      1  With t As(Select Level SrNo From dual Connect By Level<=&&H)
                                      2  Select case when rownum=&&H Or rownum=1 then
                                      3            tRIM(tri)
                                      4            Else
                                      5            Rpad('*',Length(tRIM(tri))-1)||'*'
                                      6     End
                                      7  from
                                      8  (Select Replace(sys_connect_by_path('*'||',',' '),',','')Tri
                                      9* From t Start With srno=1 Connect By Prior SrNo=Srno-1)
                                    SQL> /
                                    old   1: With t As(Select Level SrNo From dual Connect By Level<=&&H)
                                    new   1: With t As(Select Level SrNo From dual Connect By Level<=10)
                                    old   2: Select case when rownum=&&H Or rownum=1 then
                                    new   2: Select case when rownum=10 Or rownum=1 then
                                    
                                    CASEWHENROWNUM=10ORROWNUM=1THENTRIM(TRI)ELSERPAD('*',LENGTH(TRIM(TRI))-1)||'*'END
                                    ----------------------------------------------------------------------------------------------------
                                    *
                                    * *
                                    *   *
                                    *     *
                                    *       *
                                    *         *
                                    *           *
                                    *             *
                                    *               *
                                    * * * * * * * * * *
                                      1  With t As(Select Level SrNo From dual Connect By Level<=&&H)
                                      2  Select (case when rownum=&&H Or rownum=1 then
                                      3            tRIM(tri)
                                      4            Else
                                      5            Rpad('*',Length(tRIM(tri))-1)||'*'
                                      6     End)tri
                                      7  from
                                      8  (Select Replace(sys_connect_by_path('*'||',',' '),',','')Tri
                                      9  From t Start With srno=1 Connect By Prior SrNo=Srno-1
                                     10* order by 1 desc)
                                    SQL> /
                                    old   1: With t As(Select Level SrNo From dual Connect By Level<=&&H)
                                    new   1: With t As(Select Level SrNo From dual Connect By Level<=10)
                                    old   2: Select (case when rownum=&&H Or rownum=1 then
                                    new   2: Select (case when rownum=10 Or rownum=1 then
                                    
                                    TRI
                                    ----------------------------------------------------------------------------------------------------
                                    * * * * * * * * * *
                                    *               *
                                    *             *
                                    *           *
                                    *         *
                                    *       *
                                    *     *
                                    *   *
                                    * *
                                    *
                                    
                                    10 rows selected.
                                    And
                                      1  With t As(Select Level SrNo From dual Connect By Level<=&&H)
                                      2  Select (case when  rownum=1 Or rownum=&&H then
                                      3            Rpad('*',&&H*2-2,'*')||'*'
                                      4         Else
                                      5         Rpad('*',Length(tRIM(tri))-1)||'*'||
                                      6  Lpad('*',&&H*2-1-Length(Rpad('*',Length(tRIM(tri))-1)||'*'))
                                      7     End)tri
                                      8  from
                                      9  (Select Replace(sys_connect_by_path('*'||',',' '),',','')Tri
                                     10  From t Start With srno=1 Connect By Prior SrNo=Srno-1
                                     11* order by 1 desc)
                                    SQL> /
                                    old   1: With t As(Select Level SrNo From dual Connect By Level<=&&H)
                                    new   1: With t As(Select Level SrNo From dual Connect By Level<=10)
                                    old   2: Select (case when  rownum=1 Or rownum=&&H then
                                    new   2: Select (case when  rownum=1 Or rownum=10 then
                                    old   3:           Rpad('*',&&H*2-2,'*')||'*'
                                    new   3:           Rpad('*',10*2-2,'*')||'*'
                                    old   6: Lpad('*',&&H*2-1-Length(Rpad('*',Length(tRIM(tri))-1)||'*'))
                                    new   6: Lpad('*',10*2-1-Length(Rpad('*',Length(tRIM(tri))-1)||'*'))
                                    
                                    TRI
                                    ----------------------------------------------------------------------------------------------------
                                    *******************
                                    *               * *
                                    *             *   *
                                    *           *     *
                                    *         *       *
                                    *       *         *
                                    *     *           *
                                    *   *             *
                                    * *               *
                                    *******************
                                    
                                    10 rows selected.
                                    Edited by: user291283 on Sep 30, 2009 6:05 PM

                                    Edited by: user291283 on Sep 30, 2009 6:12 PM