1 2 Previous Next 20 Replies Latest reply on Jul 16, 2018 11:26 AM by Sven W.

    How to get below ouput ?

    913349

      Hi Team,

      i am using oracle 11g.

       

      I have value like 'DAMBY'

       

      i want output like

      D

      A

      M

      B

      Y

       

      Regards

      Damby

        • 1. Re: How to get below ouput ?
          KayK

          Hi Damby,

          what about this running in sql*plus

           

          < sys:gr5@sx106 > col output for a1 wrap
          < sys:gr5@sx106 > select 'DAMBY' output from dual;
          
          O
          -
          D
          A
          M
          B
          Y
          

           

          But i expect you need something procedural, right ?

           

          declare
           test varchar2(5) := 'DAMBY';
          begin
           for i in 1..length(test) loop
             dbms_output.put_line ( substr(test, i, 1) );
           end loop;
          end;
          /
          

           

          regards

          Kay

          • 2. Re: How to get below ouput ?
            mNem

            SQL> variable userinput varchar2(15);

            SQL> exec :userinput := 'DAMBY';

             

            SQL> SELECT substr(:userinput, level, 1) c FROM dual connect by level <= length(:userinput);

             

            C

            -

            D

            A

            M

            B

            Y

            • 3. Re: How to get below ouput ?
              Etbin

              Maybe

               

              with

              verticalizer(string,letters) as

              (select substr(str,2),substr(str,1,1)

                 from (select 'DAMBY' str

                         from dual

                      )

              union all

              select substr(string,2),substr(string,1,1)

                 from verticalizer

                where string is not null

              )

              select letters

                from verticalizer

               

              LETTERS
              D
              A
              M
              B
              Y

               

              Regards

               

              Etbin

              • 4. Re: How to get below ouput ?
                BluShadow

                Lots of ways to achieve this... how about some recursive subquery factoring...

                 

                SQL> ed
                Wrote file afiedt.buf

                  1  with t as (select '&data' as data from dual)
                  2      ,r(ch, rem) as (
                  3        select substr(data,1,1) as ch
                  4              ,substr(data,2) as rem
                  5        from  t
                  6        union all
                  7        select substr(r.rem,1,1) as ch
                  8              ,substr(r.rem,2)
                  9        from  r
                10        where  r.rem is not null
                11        )
                12  select ch
                13* from  r
                SQL> /
                Enter value for data: DAMBY
                old  1: with t as (select '&data' as data from dual)
                new  1: with t as (select 'DAMBY' as data from dual)

                C
                -
                D
                A
                M
                B
                Y

                • 5. Re: How to get below ouput ?
                  Solomon Yakobson

                  This was already suggested by Etbin. Just for fun, XMLTABLE solution:

                   

                  select  *

                    from  xmltable(

                                   'for $c in string-to-codepoints("DAMBY")

                                      return codepoints-to-string($c)

                                   '

                                  )

                  /

                   

                  COLUMN_VALUE

                  ------------

                  D

                  A

                  M

                  B

                  Y

                   

                  SQL>

                   

                  SY.

                  • 6. Re: How to get below ouput ?
                    jaramill

                    I like this one because it's not using HIERARCHICAL SQL functionality (i..e connect by).

                    • 7. Re: How to get below ouput ?
                      842395

                      Hi

                       

                      use below query to get results.

                       

                      select substr('DAMBY',LEVEL,1) FROM DUAL CONNECT BY LEVEL<=LENGTH('DAMBY');

                      • 8. Re: How to get below ouput ?
                        odie_63

                        Your requirement is not clear as it may be interpreted in two different ways.

                         

                        1- Extracting each character in its own relational row

                        2- Formatting the string by inserting a linefeed between each character

                         

                        Which one do you want?

                         

                        Solutions have been given already for #1.

                         

                        Here's an example for #2 :

                         

                        select rtrim(regexp_replace('DAMBY', '(.)', '\1'||chr(10)), chr(10)) as output
                        from dual;
                        
                        OUTPUT  
                        ---------
                        D        
                        A        
                        M        
                        B
                        Y       
                        
                        • 9. Re: How to get below ouput ?
                          jaramill

                          842395 wrote:

                           

                          Hi

                           

                          use below query to get results.

                           

                          select substr('DAMBY',LEVEL,1) FROM DUAL CONNECT BY LEVEL<=LENGTH('DAMBY');

                          The user "mNem" (2nd reply, 3rd post overall) already gave the exact same solution as you did (further up).

                          • 10. Re: How to get below ouput ?
                            Solomon Yakobson

                            Or, using SQL*Plus (just for fun ):

                             

                            SQL> column damby format a1

                            SQL> select  'DAMBY' damby

                              2    from  dual

                              3  /

                             

                            D

                            -

                            D

                            A

                            M

                            B

                            Y

                             

                            SQL>

                             

                            SY

                            • 11. Re: How to get below ouput ?
                              BluShadow

                              Solomon Yakobson wrote:

                               

                              Or, using SQL*Plus (just for fun ):

                               

                              SQL> column damby format a1

                              SQL> select 'DAMBY' damby

                              2 from dual

                              3 /

                               

                              D

                              -

                              D

                              A

                              M

                              B

                              Y

                               

                              SQL>

                               

                              SY

                               

                               

                              KayK showed that in the first reply. 

                              • 12. Re: How to get below ouput ?
                                jaramill

                                haha!  I knew his solution looked familiar   We're all tripping over ourselves to show "another way to skin a cat"

                                • 13. Re: How to get below ouput ?
                                  Sven W.

                                  Much easier with the model clause!

                                   

                                   

                                  select letter
                                  from dual
                                  model
                                    dimension by (1 id)
                                    measures ('D' letter, 'DAMBY' val)
                                    rules iterate (5)
                                      (letter[iteration_number+1] = substr(val[1], iteration_number+1,1)
                                      );
                                  

                                   

                                  Ok here i cheated lightly because I iterate exactly 5 times.  It also works without knowing that info beforehand.

                                   

                                  select letter
                                  from dual
                                  model
                                    dimension by (1 id)
                                    measures ('D' letter, 'DAMBY' val)
                                    rules iterate (1000) until (iteration_number+1 >= length(val[1]))
                                      (letter[iteration_number+1] = substr(val[1], iteration_number+1,1)
                                      );
                                  
                                  • 14. Re: How to get below ouput ?
                                    mathguy

                                    jaramill wrote:

                                     

                                    I like this one because it's not using HIERARCHICAL SQL functionality (i..e connect by).

                                     

                                     

                                    The faster way, which avoids recursion altogether, is to cross join to a large enough table (or view), one that we can guarantee beforehand will have more rows than there are characters in the input, and to use ROWNUM to range over the positions within the input string. Something like this:

                                     

                                    select substr(str, rownum, 1)

                                    from   (select 'DAMBY' as str from dual)

                                           cross join

                                           user_objects

                                    where  rownum <= length(str)

                                    ;

                                     

                                    Actually this demonstrates a poor implementation of the idea: on my very small DB installation, USER_OBJECTS has just 34 rows. Not good enough for this purpose. ALL_OBJECTS (which is often used with this approach) has over 65,000 rows, and should certainly be enough for any VARCHAR2 input, even with extended max length. One way or another, before using this strategy one needs a large enough table (or view).

                                     

                                    Interesting question: Suppose we have a table or view (like USER_OBJECTS) with 34 rows, and another (I have such a table which I used in another thread) with 100 million rows. Does it matter for performance which one we use (assuming the small table/view is still large enough to produce the correct output)? Will rows continue to be generated from the "helper" table or view, even after the upper bound in the WHERE condition is reached, wasting time on a very large amount of useless work? Happily, my quick and informal test shows that it doesn't matter - so we may as well use a very large table to be safe.

                                    1 2 Previous Next