1 2 3 Previous Next 32 Replies Latest reply: Sep 30, 2009 9:23 AM by cd_2 RSS

    Draw SQUARE using SQL

    roboracle
      How can I write an SQL to generate a square for a specified length?. Using only SQL (no PL SQL).

      Assumption - Two characters in the horizontal line is equal to one character in vertical line.

      Example

      Suppose if length of square is 7 then there will be 14 character in horizontal line and 7 character in vertical line.
      **************
      *            *
      *            *
      *            *
      *            *
      *            *
      **************
      Please help.

      Edited by: user10681556 on Sep 10, 2009 1:26 AM
        • 1. Re: Draw SQUARE using SQL
          21205
          amazing what teachers come up with these days....


          tip: RPAD
          • 2. Re: Draw SQUARE using SQL
            436423
            Well whoring SQL is probably illegal but I can write it. Assuming SQL*Plus to use substitution variables, try:
            with
              h as (
                select * from (select level l from dual connect by level <= &height)  
              )
            select
              decode(
                rownum,
                1, rpad('*', (&&height*2), '*'),
                &&height, rpad('*', (&&height*2), '*'),
                rpad('*', (&&height*2)-1)||'*'
              ) as square
            from
              h
            ;
            D'oh! the output:
            SQL> with
              2    h as (
              3      select * from (select level l from dual connect by level <= &&height)
              4    )
              5  select
              6    decode(
              7      rownum,
              8      1, rpad('*', (&&height*2), '*'),
              9      &&height, rpad('*', (&&height*2), '*'),
             10      rpad('*', (&&height*2)-1)||'*'
             11    ) as square
             12  from
             13    h
             14  ;
            Enter value for height: 7
            
            SQUARE
            --------------
            **************
            *            *
            *            *
            *            *
            *            *
            *            *
            **************
            
            7 rows selected.
            
            SQL>
            Edited by: Chris Poole on Sep 10, 2009 7:06 PM Forgot the output
            • 3. Re: Draw SQUARE using SQL
              21205
              SQL> var sz number
              SQL> 
              SQL> begin
                2     :sz := 7;
                3  end;
                4  /
              
              PL/SQL procedure successfully completed.
              
              SQL> 
              SQL> select case when rownum = 1 or rownum = :sz
                2              then rpad ('*', :sz * 2, '*')
                3              else '*'||rpad (' ', (:sz * 2) - 2)||'*'
                4         end square
                5    from (select 1 from dual
                6   connect by level <= :sz)
                7  /
              
              SQUARE
              ----------------------------------------------------------
              **************
              *            *
              *            *
              *            *
              *            *
              *            *
              **************
              
              7 rows selected.
              • 4. Re: Draw SQUARE using SQL
                21205
                or (just for fun)
                SQL> var sz number
                SQL> 
                SQL> begin
                  2     :sz := 10;
                  3  end;
                  4  /
                
                PL/SQL procedure successfully completed.
                
                SQL> 
                SQL> select str square
                  2    from dual
                  3   model
                  4   dimension by (rownum as rn)
                  5   measures (cast ('*' as varchar2(50)) str)
                  6   rules (str [1] = rpad (str[cv()], :sz * 2, '*')
                  7         ,str [for rn from 2 to :sz - 1 increment 1] = '*'||rpad (' ', (:sz * 2) - 2)||'*'
                  8         ,str [:sz] = rpad ('*', :sz * 2, '*')
                  9         )
                 10   order by rn
                 11  /
                
                SQUARE
                --------------------------------------------------
                ********************
                *                  *
                *                  *
                *                  *
                *                  *
                *                  *
                *                  *
                *                  *
                *                  *
                ********************
                
                10 rows selected.
                
                SQL> 
                ... but I'm sure Rob could come up with a more elegant solution ;)
                • 5. Re: Draw SQUARE using SQL
                  Rob van Wijk
                  Alex Nuijten wrote:
                  ... but I'm sure Rob could come up with a more elegant solution ;)
                  Not really.

                  Only one rule less:
                  SQL> select s square
                    2    from dual
                    3   model
                    4         dimension by (0 i)
                    5         measures (cast(null as varchar2(100)) s)
                    6         rules
                    7         ( s[for i from 1 to :sz increment 1] = '*' || rpad (' ',2*:sz - 2) || '*'
                    8         , s[i in (1,:sz)] = replace(s[cv()],' ','*')
                    9         )
                   10  /
                  
                  SQUARE
                  -------------------------------------------------------------------------------------
                  
                  ********************
                  *                  *
                  *                  *
                  *                  *
                  *                  *
                  *                  *
                  *                  *
                  *                  *
                  *                  *
                  ********************
                  
                  11 rijen zijn geselecteerd.
                  Regards,
                  Rob.
                  • 6. Re: Draw SQUARE using SQL
                    BluShadow
                    Slightly more tight piece of SQL...
                    SQL> var sz number;
                    SQL> exec :sz := 7;
                    
                    PL/SQL procedure successfully completed.
                    
                    SQL> ed
                    Wrote file afiedt.buf
                    
                      1  select rpad('*', (:sz*2)-1, decode(mod(rownum,:sz-1),1,'*',' '))||'*' as square
                      2  from dual
                      3* connect by rownum <= :sz
                    SQL> /
                    
                    SQUARE
                    ------------------------------------------------------------------------------------
                    **************
                    *            *
                    *            *
                    *            *
                    *            *
                    *            *
                    **************
                    
                    7 rows selected.
                    
                    SQL> exec :sz := 10;
                    
                    PL/SQL procedure successfully completed.
                    
                    SQL> /
                    
                    SQUARE
                    ------------------------------------------------------------------------------------
                    ********************
                    *                  *
                    *                  *
                    *                  *
                    *                  *
                    *                  *
                    *                  *
                    *                  *
                    *                  *
                    ********************
                    
                    10 rows selected.
                    
                    SQL>
                    • 7. Re: Draw SQUARE using SQL
                      roboracle
                      Wow...a bunch of ways. Alex's way was new to me. Thanks Alex.

                      Chris's was also good (I was close to this solution at my best, only forgot to ask for user input i.e. &&).

                      I can only laugh at myself by seeing my code to do the same...

                      SELECT 
                      '**************' 
                      || chr(13) || chr(10) || 
                      '*            *'
                      || chr(13) || chr(10) || 
                      '*            *'
                      || chr(13) || chr(10) || 
                      '*            *'
                      || chr(13) || chr(10) || 
                      '*            *'
                      || chr(13) || chr(10) || 
                      '*            *'
                      || chr(13) || chr(10) || 
                      '**************'
                      AS "square" FROM dual;
                      ;)
                      • 8. Re: Draw SQUARE using SQL
                        MichaelS
                        Another one for fun:
                        SQL> var n number
                        SQL>
                        SQL> exec :n := 7
                        
                        PL/SQL procedure successfully completed.
                        
                        SQL>
                        SQL> select *
                          2   from xmltable('for $i in 1 to xs:int($n)
                          3                       return string-join ((for $j in 1 to xs:int(2*$n)
                          4                                           return if (not($i=(1,$n)) and not($j=(1,2*$n))) then " " else "*"),"")'
                          5                                           passing :n as "n"
                          6                                           columns square varchar2(20) path '.'
                          7                                           )
                          8  /
                        
                        SQUARE
                        --------------------
                        **************
                        *            *
                        *            *
                        *            *
                        *            *
                        *            *
                        **************
                        
                        7 rows selected.
                        
                        SQL>
                        • 9. Re: Draw SQUARE using SQL
                          21205
                          user10681556 wrote:
                          Wow...a bunch of ways. Alex's way was new to me. Thanks Alex.
                          ... and still didn't get any points for it ;)

                          And what's wrong with your solution? It's just a different way of achieving the same thing,... although your query returns 1 row while the other solutions return multiple rows.
                          • 10. Re: Draw SQUARE using SQL
                            21205
                            ... you're getting an extra record in your resultset (for i = 0)


                            ... I'm having a hard time understanding this
                             s[i in (1,:sz)] = replace(s[cv()],' ','*')
                            ... must be a lack of coffee... ;)
                            • 11. Re: Draw SQUARE using SQL
                              BluShadow
                              Well, if we want it just as one row returned...
                              SQL> ed
                              Wrote file afiedt.buf
                              
                                1  select replace(ltrim(sys_connect_by_path(square,','),','),',',chr(10)) as square
                                2  from (
                                3    select rpad('*', (:sz*2)-1, decode(mod(rownum,:sz-1),1,'*',' '))||'*' as square, rownum rn
                                4    from dual
                                5    connect by rownum <= :sz
                                6    )
                                7  where connect_by_isleaf = 1
                                8  connect by rn = prior rn + 1
                                9* start with rn = 1
                              SQL> /
                              
                              SQUARE
                              -------------------------------------------------------------------------------------------------
                              ********************
                              *                  *
                              *                  *
                              *                  *
                              *                  *
                              *                  *
                              *                  *
                              *                  *
                              *                  *
                              ********************
                              
                              
                              SQL> exec :sz := 12;
                              
                              PL/SQL procedure successfully completed.
                              
                              SQL> /
                              
                              SQUARE
                              -------------------------------------------------------------------------------------------------
                              ************************
                              *                      *
                              *                      *
                              *                      *
                              *                      *
                              *                      *
                              *                      *
                              *                      *
                              *                      *
                              *                      *
                              *                      *
                              ************************
                              
                              
                              SQL>
                              • 12. Re: Draw SQUARE using SQL
                                BluShadow
                                Alex Nuijten wrote:
                                ... you're getting an extra record in your resultset (for i = 0)


                                ... I'm having a hard time understanding this
                                s[i in (1,:sz)] = replace(s[cv()],' ','*')
                                ... must be a lack of coffee... ;)
                                It's just replacing the spaces for asterisks on the first and last row. You definitely need coffee. ;)
                                • 13. Re: Draw SQUARE using SQL
                                  Rob van Wijk
                                  Alex Nuijten wrote:
                                  ... you're getting an extra record in your resultset (for i = 0)
                                  Good catch. The dimension clause should be changed to "dimension by (1 i)".
                                  ... I'm having a hard time understanding this
                                  s[i in (1,:sz)] = replace(s[cv()],' ','*')
                                  Blu already explained this one. Or is there a particular piece in that rule that's confusing you?

                                  Regards,
                                  Rob.
                                  • 14. Re: Draw SQUARE using SQL
                                    Prazy
                                    you guyz are awesome!!

                                    or, This!!
                                    ETLTEST@orcl> exec :sz:=19
                                    
                                    PL/SQL procedure successfully completed.
                                    
                                    Elapsed: 00:00:00.01
                                    ETLTEST@orcl> select (case when mod(:sz,2)=0 then
                                      2  'Odd Number Pls!'
                                      3  else
                                      4  (case when rownum=ceil(:sz/2) then
                                      5  rpad('*',:sz,'*')
                                      6  else
                                      7  decode(rownum,1,
                                      8  rpad(' ',ceil(:sz/2)-1,' ')||'*',
                                      9  rpad(' ',ceil(:sz/2)-rownum,' ')||'*'||rpad(' ',(rownum-1)+(rownum-2),' ')||'*') end)end) "Triangle Output" from dual
                                     10  connect by rownum<=ceil(:sz/2)
                                     11  /
                                    
                                    Triangle Output
                                    ----------------------------------------
                                             *
                                            * *
                                           *   *
                                          *     *
                                         *       *
                                        *         *
                                       *           *
                                      *             *
                                     *               *
                                    *******************
                                    
                                    10 rows selected.
                                    
                                    Elapsed: 00:00:00.01
                                    Oops... Triangle....

                                    can some one simplify this :(
                                    1 2 3 Previous Next