5 Replies Latest reply: Mar 15, 2013 11:00 AM by user346369 RSS

    lov holds substr(instr(column,' ') and substr(instr(column,' ',1,2)

    salute-Salem
      db and dev 10g rel2 ,
      hi all,
      i've a column ename holds these values for instance :
      matt jack neck
      mathew black new
      maw batrick solivan


      i want to create a lov holds the values :
      matt
      maw
      mathew
      matt jack
      mathew black
      maw batrick

      how could i write this query ? , i can write it in sqlplus but the result is shown in two columns ,
      and i want it in the lov one column .?

      thanks in advance
        • 1. Re: lov holds substr(instr(column,' ') and substr(instr(column,' ',1,2)
          user346369
          <pre>with tmp as (
          select 'matt jack neck' as C1 from dual
          union all select 'mathew black new' from dual
          union all select 'maw batrick solivan' from dual
          union all select 'ABC 123 DEF 456' from dual )
          Select substr(C1,1,instr(C1,' ')) from tmp
          union all
          Select substr(C1,1,instr(C1,' ',1,2)) from tmp;</pre>

          Output:
          <pre>matt
          mathew
          maw
          ABC
          matt jack
          mathew black
          maw batrick
          ABC 123</pre>
          • 2. Re: lov holds substr(instr(column,' ') and substr(instr(column,' ',1,2)
            salute-Salem
            thanks man , but i want to know something please ,

            i've tested this query and i think it worked :-- i think because it does not show the number of rows , and it seems there is a space under records . i do not know if it is normal or not .


            select substr(ename,1,instr(ename,' ')) from test2
            union
            select substr(ename,1,instr(ename,' ',1,2)) from test2
            /

            and i think that what you wrote is called the "with clause" , or "inline veiw" , and i heard before that any thing you can do with "inline views , with clause and analytics" can be done with native sql , like joins and set operators "union,union all,..."

            ?
            is this right ?

            thanks man
            • 3. Re: lov holds substr(instr(column,' ') and substr(instr(column,' ',1,2)
              user346369
              The "with tmp as (select.....from dual) is ONLY THERE to create test data.

              You would leave all of that out, using the only the last two Select statements, and use "from &lt;YourTableName"
              i think because it does not show the number of rows
              In SQL Plus:

              <pre>SQL> SET FEEDBACK ON
              SQL> with tmp as (
              2 select 'matt jack neck' as C1 from dual
              3 union all select 'mathew black new' from dual )
              4 Select substr(C1,1,instr(C1,' ')) from tmp
              5 union all
              6 Select substr(C1,1,instr(C1,' ',1,2)) from tmp;

              SUBSTR(C1,1,INST
              ----------------
              matt
              mathew
              matt jack
              mathew black

              4 rows selected.

              SQL> </pre>
              • 4. Re: lov holds substr(instr(column,' ') and substr(instr(column,' ',1,2)
                salute-Salem
                i'm sorry but i do not get it ,
                is my statement true or not ?
                , and if you could do it with the column name(ename) and the table name(test2) ,please

                - what is "set feedback on" , what should it does ?

                thanks

                Edited by: newbi_egy on Mar 15, 2013 4:42 AM
                • 5. Re: lov holds substr(instr(column,' ') and substr(instr(column,' ',1,2)
                  user346369
                  - what is "set feedback on" , what should it does ?
                  i've tested this query and i think it worked :-- i think because it <font color=red>does not show the number of rows</font> , and it seems there is a space under records . i do not know if it is normal or not .
                  SET FEEDBACK ON is a SQL Plus command that shows you the number of rows.

                  (Maybe you should do a little Google searching when you don't understand some of these things)

                  if you could do it with the column name(ename) and the table name(test2)
                  Why don't YOU try it?

                  <pre>Select substr(<font color=red>ename</font>,1,instr(<font color=red>ename</font>,' ')) from <font color=red>test2</font>
                  union all
                  Select substr(<font color=red>ename</font>,1,instr(<font color=red>ename</font>,' ',1,2)) from <font color=red>test2</font>;</pre>