1 2 Previous Next 29 Replies Latest reply: Nov 20, 2012 3:48 AM by Kalpataru Go to original post RSS
      • 15. Re: Query to Reverse of a word
        Kalpataru
        Hi ranit B,
        we have a table which is stored the product names and product descriptions
        but in the description part a lots of strings having repeated for example i have given there
        two examples like
        FREE PRODUCT SIZE 10 M
        FREE PRODUCT SIZE 20 M
        FREE PRODUCT SIZE 30 M
        CELLIN INSERT THICK 10M
        CELLIN INSERT THIN 20M
        CELLIN INSERT THREAD 30M
        . so i don't want to display those repeated strings i want to only show the
        non-repeated strings.
        Here my problem is i don't know what will be the string
        why because lots of product descriptions are there.
        • 16. Re: Query to Reverse of a word
          ranit B
          FREE PRODUCT SIZE 10 M
          CELLIN INSERT THICK 10M
          Here which is the description and which is the Product name ??
          i don't know what will be the string why because lots of product descriptions are there.
          What do you mean by this?
          Do you mean to say that you don't know what will be the output be like??

          Edited by: ranit B on Nov 12, 2012 10:50 AM
          • 17. Re: Query to Reverse of a word
            Manik
            Ranit,

            Thx buddy for pointing that, I modified my query a bit for multi row viability.
            WITH t AS
                    (SELECT 'MY NAME IS RAMA' dc FROM DUAL
                     UNION ALL
                     SELECT 'i am good' dc FROM DUAL
                     union all
                     select 'test case new' from dual),
                 t1 AS
                    (SELECT ROWNUM AS id, a.*
                       FROM t a),
                 t2 AS
                    (    SELECT REGEXP_SUBSTR (dc,
                                               '[^ ]+',
                                               1,
                                               LEVEL)
                                   dc,
                                LEVEL order_id,
                                id
                           FROM t1
                     CONNECT BY LEVEL <= REGEXP_COUNT (dc, ' ') + 1
                       GROUP BY id, dc, LEVEL)
              SELECT listagg (dc, ' ') WITHIN GROUP (ORDER BY id, order_id DESC)
                FROM t2
            GROUP BY id;
            output
            RAMA IS NAME MY
            good am i
            new case test
            Cheers,
            Manik
            • 18. Re: Query to Reverse of a word
              BluShadow
              Solomon Yakobson wrote:
              PRIOR DBMS_RANDOM.RANDOM IS NOT NULL produces adifferent random number on each level thus avoiding CYCLE.

              SY.
              Interesting statement SY. How can we be sure that DBMS_RANDOM.RANDOM produced a different random number on each level? That would be like saying random numbers will be unique.... eeek!
              • 19. Re: Query to Reverse of a word
                Manik
                Blu Shadow,

                Trying to learn the query of SY.
                Just tried to modify SY query here... is this correct? can we use this way?
                with t as (
                           select 'MY NAME IS RAMA' s from dual union all
                           select 'HE IS GOOD' from dual
                          )
                select  ltrim(sys_connect_by_path(regexp_substr(s,'\w+',1,regexp_count(s,'\w+') + 1 - level),' ')) r
                  from  t
                  where connect_by_isleaf = 1
                  connect by s = prior s
                --        and prior dbms_random.random is not null   -- commented to check
                        and prior sys_guid() is not null
                        and regexp_substr(s,'\w+',1,level) is not null
                / 
                output:
                --------------------------
                GOOD IS HE
                RAMA IS NAME MY


                Cheers,
                Manik.
                • 20. Re: Query to Reverse of a word
                  Solomon Yakobson
                  BluShadow wrote:
                  That would be like saying random numbers will be unique.... eeek!
                  Yes, there is a theoretical chance to get a duplicate. To make it bulletproof:
                  with t as (
                             select 'MY NAME IS RAMA' s from dual union all
                             select 'HE IS GOOD' from dual
                            )
                  select  ltrim(sys_connect_by_path(regexp_substr(s,'\w+',1,regexp_count(s,'\w+') + 1 - level),' ')) r
                    from  t
                    where connect_by_isleaf = 1
                    connect by s = prior s
                          and prior sys_guid() is not null
                          and regexp_substr(s,'\w+',1,level) is not null
                  /
                  
                  R
                  -----------------
                  GOOD IS HE
                  RAMA IS NAME MY
                  
                  SQL> 
                  SY.
                  • 21. Re: Query to Reverse of a word
                    Stew Ashton
                    You really should test for the condition where there is no token. I know it's easy to fix...
                    with t as (
                       select 'MY' s from dual union all
                       select 'HE IS GOOD' from dual
                    )
                    select x.result
                    from t
                       , xmltable(
                           'string-join(reverse(ora:tokenize($str,"\s+"))," ")'
                           passing s as "str"
                           columns result varchar2(4000) path '.'
                         ) x
                    ;
                    
                    Error report:
                    SQL Error: ORA-19176: FORX0003: regular expression matches zero-length string
                    • 22. Re: Query to Reverse of a word
                      Stew Ashton
                      Your solution is pretty slow. Have you tried it with 1000 rows?

                      This variant is faster:
                      select (
                        select ltrim(sys_connect_by_path(regexp_substr(s,'\w+',1,regexp_count(s,'\w+') + 1 - level),' ')) r
                        from  dual
                        where connect_by_isleaf = 1
                        connect by level <= length(s) + 1 - length(replace(s, ' ', ''))
                      ) answer
                      from t;
                      And this variant is faster still:
                      with data as (
                        select ' ' || s || ' ' s,
                        length(s) - length(replace(s, ' ', '')) + 2 lvl from t
                      )
                      select (
                        select sys_connect_by_path(
                          substr(s,
                            instr(s,' ',1,lvl-level) + 1,
                            instr(s,' ',1,lvl-level+1) - instr(s,' ',1,lvl-level) - 1
                          ),
                          ' ')
                        from dual
                        where connect_by_isleaf = 1
                        connect by level < lvl
                      ) answer
                      from data;
                      Edited by: Stew Ashton on Nov 12, 2012 7:14 PM
                      • 23. Re: Query to Reverse of a word
                        Solomon Yakobson
                        Stew Ashton wrote:

                        This variant is faster
                        Maybe, but is it correct?
                        SQL> with t as (
                          2             select 'MY  NAME  IS  RAMA' s from dual union all
                          3             select 'HE    IS         GOOD' from dual
                          4            )
                          5  select (
                          6    select ltrim(sys_connect_by_path(regexp_substr(s,'\w+',1,regexp_count(s,'\w+') + 1 - level),' ')) r
                          7    from  dual
                          8    where connect_by_isleaf = 1
                          9    connect by level <= length(s) + 1 - length(replace(s, ' ', ''))
                         10  ) answer
                         11  from t
                         12  /
                          select ltrim(sys_connect_by_path(regexp_substr(s,'\w+',1,regexp_count(s,'\w+') + 1 - level),' ')) r
                                                                                                             *
                        ERROR at line 6:
                        ORA-01428: argument '0' is out of range
                        
                        
                        SQL> 
                        SY.
                        • 24. Re: Query to Reverse of a word
                          Stew Ashton
                          Touché !
                          • 25. Re: Query to Reverse of a word
                            chris227
                            On 11.2 upwards you might consider recursive subquery instead of connect by. May be it's faster too ...
                            with testdata as (
                            SELECT 'MY NAME IS RAMA' val FROM DUAL UNION ALL
                            SELECT 'i am good' FROM DUAL union all
                            select 'test case new' from dual
                            )
                            ,r (val, s, str) as (
                            select
                              val
                            , rtrim(replace (val, substr(val, instr(val,' ',-1)))) s
                            , substr(val, instr(val,' ',-1) + 1) str
                            from testdata
                            union all
                            select
                              val
                            , trim(replace (s, substr(s, instr(s,' ',-1))))
                            , str ||' '|| substr(s, instr(s,' ',-1) + 1)
                            from r
                            where
                            length(s) > 0
                            )
                            
                            select 
                             val
                            ,str
                            from r
                            where
                            s is null
                            
                            VAL STR 
                            i am good good am i 
                            test case new new case test 
                            MY NAME IS RAMA RAMA IS NAME MY 
                            • 26. Re: Query to Reverse of a word
                              Kalpataru
                              Hi ranit B,
                              FREE PRODUCT SIZE 10 M
                              CELLIN INSERT THICK 10M
                              these are the descriptions not the product name i want only the none common parts.
                              for example SIZE 10 M and THICK 10M
                              there are lots of descriptions having these type of combinations for example some strings are common
                              i don't want the common parts which are repeated by nature for multiple rows.
                              i have just given you these two examples.
                              for example
                              if my string is
                              123 xyz
                              123 abc
                              123 mno
                              the output will be
                              xyz
                              abc
                              mno
                              • 27. Re: Query to Reverse of a word
                                ranit B
                                Just tell me what is the expected o/p of the below -
                                with xx as(
                                  select 'FREE' c1,'PRODUCT' c2,'SIZE' c3,'10 M' c4 from dual union all
                                  select 'FREE' c1,'PRODUCT' c2,'SIZE' c3,'20 M' c4 from dual union all
                                  select 'FREE' c1,'PRODUCT' c2,'SIZE' c3,'20 M' c4 from dual union all
                                  select 'ABC' c1,'123' c2,'ORACLE' c3,'123' c4 from dual union all
                                  select 'ABC' c1,'123' c2,'XYZ' c3,'123' c4 from dual union all
                                  select 'ABC' c1,'123' c2,'KLM' c3,'123' c4 from dual 
                                )
                                select c3,c4 from xx;
                                • 28. Re: Query to Reverse of a word
                                  user13328581
                                  interesting reverse solution for words....
                                  • 29. Re: Query to Reverse of a word
                                    Kalpataru
                                    hi ranit B,
                                    I have just given you the example
                                    with xx as(
                                    select 'FREE' c1,'PRODUCT' c2,'SIZE' c3,'10 M' c4 from dual union all
                                    select 'FREE' c1,'PRODUCT' c2,'SIZE' c3,'20 M' c4 from dual union all
                                    select 'FREE' c1,'PRODUCT' c2,'SIZE' c3,'20 M' c4 from dual union all
                                    select 'ABC' c1,'123' c2,'ORACLE' c3,'123' c4 from dual union all
                                    select 'ABC' c1,'123' c2,'XYZ' c3,'123' c4 from dual union all
                                    select 'ABC' c1,'123' c2,'KLM' c3,'123' c4 from dual
                                    )
                                    select c3,c4 from xx;

                                    output will be
                                    -------------------
                                    size 10m
                                    size 10m
                                    size 10m

                                    oracle 123
                                    xyz 123
                                    klm 123

                                    but here i want to use the column name productdescription
                                    why because i don't know what will be my next string will be.
                                    1 2 Previous Next