1 2 3 4 Previous Next 47 Replies Latest reply on Mar 27, 2013 3:47 PM by user134954

    TRIM and regexp_Replace combined

    user134954
      Hi there,

      a beginner question:

      I go the following string:

      :TEST:TEST2:

      I dont want to see leading and trailing : so i do

      Select trim (both ':' from ':TEST:TEST2:') from dual

      Now I also want to regexp_replace the ':' by a ','



      The solution should look like (Test,Test2)

      What to do now? Thx in advance [Oracle 11g]
        • 1. Re: TRIM and regexp_Replace combined
          RPuttagunta
          Like so?
          SQL> SELECT REPLACE(TRIM(trailing ':' FROM(TRIM(leading ':' FROM ':TEST:TEST2:'))), ':', ',') FROM dual;
          
          REPLACE(TR
          ----------
          TEST,TEST2
          Edited by: RPuttagunta on Feb 27, 2013 9:49 AM
          • 2. Re: TRIM and regexp_Replace combined
            RPuttagunta
            After reading the documentation, the better version is this:
            SQL> SELECT REPLACE(TRIM(':' FROM ':TEST:TEST2:'), ':', ',') FROM dual;
            
            REPLACE(TR
            ----------
            TEST,TEST2
            • 3. Re: TRIM and regexp_Replace combined
              Frank Kulash
              Hi,
              Thorsten wrote:
              Hi there,

              a beginner question:

              I go the following string:

              :TEST:TEST2:

              I dont want to see leading and trailing : so i do

              Select trim (both ':' from ':TEST:TEST2:') from dual

              Now I also want to regexp_replace the ':' by a ','



              The solution should look like (Test,Test2)

              What to do now? Thx in advance [Oracle 11g]
              You don't need regular expressions for that. REPLACE can do this job more efficiently than REGEXP_REPLACE. Here's one way:
              SELECT     REPLACE ( TRIM (BOTH ':' FROM str)
                        , ':'
                        , ','
                        )     AS new_str
              FROM    table_x
              ;
              If you really need to use REGEXP_REPLACE for some reason, then go ahead. The first argument to REGEXP_REPLACE can be any kind of string expression, such as the string returned by TRIM.
              1 person found this helpful
              • 4. Re: TRIM and regexp_Replace combined
                chris227
                Isnt it that easy?
                replace(
                 trim (both ':' from ':TEST:TEST2:') 
                ,':',','
                )
                ;-)
                • 5. Re: TRIM and regexp_Replace combined
                  user134954
                  Thanks, but there is another Problem:

                  Now I want to have both values in ' like

                  'TEST1','TEST2','TEST3'

                  How to do this?
                  • 6. Re: TRIM and regexp_Replace combined
                    user134954
                    Need ' ' in Items
                    • 7. Re: TRIM and regexp_Replace combined
                      chris227
                      select
                      ''''||
                      replace(
                          trim ( both ':' from ':TEST:TEST2:TEST3:') 
                          ,':',''','''
                      )
                      ||'''' r
                      from dual
                      
                      R
                      "'TEST','TEST2','TEST3'"
                      And how to use this in a in-clause?
                      Doesnt work, use another approach
                      select
                          regexp_substr(':TEST:TEST2:TEST3:','[^:]+', 1, level) r
                      from dual
                      connect by
                      level < length(':TEST:TEST2:TEST3:')
                             -length(replace(':TEST:TEST2:TEST3:',':')) 
                      
                      R
                      "TEST"
                      "TEST2"
                      "TEST3"
                      Edited by: chris227 on 28.02.2013 07:03
                      1 person found this helpful
                      • 8. Re: TRIM and regexp_Replace combined
                        user134954
                        I need to reopen this again. Sorry.

                        select '('||
                        +''''||+
                        replace(
                        trim ( both ':' from ':TEST1:TEST2:')
                        +,':',''','''+
                        +)+
                        +||''''||')' r+
                        from dual

                        works fine = ('TEST1','TEST2')

                        Now I need these Values in a subselect:

                        If I try:

                        Select * from testtable where Value is in ('TEST1','TEST2')    this works fine

                        BUT if I try

                        Select * from testtable where Value is in
                        +(select '('||+
                        +''''||+
                        replace(
                        trim ( both ':' from ':TEST1:TEST2:')
                        +,':',''','''+
                        +)+
                        +||''''||')' r+
                        from dual)

                        no Data is found. Do you know why? Sry for this never ending post.

                        Edited by: Thorsten on 01.03.2013 00:45

                        Oh sry, already answered...but still trying. Thx

                        Edited by: Thorsten on 01.03.2013 00:46

                        Edited by: Thorsten on 01.03.2013 00:53
                        • 9. Re: TRIM and regexp_Replace combined
                          AlbertoFaenza
                          Hi,

                          when you put some code or output please enclose it between two lines starting with {noformat}
                          {noformat}
                          
                          i.e.:
                          {noformat}
                          {noformat}
                          SELECT ...
                          {noformat}
                          {noformat}
                          
                          What you want to do cannot be done with static SQL.
                          
                          This code:
                          Select * from testtable where Value in
                          (select '('||
                          ''''||
                          replace(
                          trim ( both ':' from ':TEST1:TEST2:')
                          ,':',''','''
                          )
                          ||''''||')' r
                          from dual);
                          correspond to something like this:
                          Select * from testtable where Value in
                          ('(''TEST1'',''TEST2'')');
                          So you are not actually checking if your value is in TEST1 or TEST2 but if your value correspond to a literal like "('TEST1','TEST2')" as a whole string.
                          
                          A solution could be found using dynamic SQL but don't know if it is really worth.
                          
                          Regards.
                          Al                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
                          • 10. Re: TRIM and regexp_Replace combined
                            chris227
                            You should read the answers given with the same accuracy as they are written.
                            I wrote:
                            "And how to use this in a in-clause?
                            Doesnt work, use another approach"

                            And i gave you an example, did you try this?
                            Why not?
                            Select * from testtable where Value is in 
                            (
                            select
                                regexp_substr(':TEST:TEST2:TEST3:','[^:]+', 1, level) r
                            from dual
                            connect by
                            level < length(':TEST:TEST2:TEST3:')
                                   -length(replace(':TEST:TEST2:TEST3:',':')) 
                            )
                            Edited by: chris227 on 01.03.2013 01:03
                            1 person found this helpful
                            • 11. Re: TRIM and regexp_Replace combined
                              user134954
                              Best community ever!

                              Thx. I should read comments!
                              • 12. Re: TRIM and regexp_Replace combined
                                user134954
                                Hi there again,

                                what to do now if i have the following:

                                :-9:TEST:TEST1:TEST2

                                I will get -9,TEST1,TEST2

                                Now I want also to trim the -9 at the beginn of the Data. But its not always there.

                                The Query is getting complicated. Do I have to do this in another connect clause? Or in a case statement?

                                Thx in advance Thorsten
                                • 13. Re: TRIM and regexp_Replace combined
                                  chris227
                                  Hi,

                                  first of all i would suggest to open a new thread instead of reusing a closed one. Probably a new one will get a wider audience.

                                  Some questions on this:

                                  Will the -9 always be at the first place or could it be in any place?
                                  May the hyphen (&#45;) be in the parts to include also or only in the parts to exclude?
                                  What is the definition of the parts to exclude? For example signed digits? Will those numbers build with one digit only or could there be more?
                                  • 14. Re: TRIM and regexp_Replace combined
                                    Manik
                                    Copying Chris's solution..
                                    extending it with another number as well....
                                    SELECT *
                                      FROM (    SELECT REGEXP_SUBSTR (':4,:-9:TEST:TEST2:TEST3:',
                                                                      '[^:]+',
                                                                      1,
                                                                      LEVEL)
                                                          r
                                                  FROM DUAL
                                            CONNECT BY LEVEL <
                                                          LENGTH (':4,:-9:TEST:TEST2:TEST3:')
                                                          - LENGTH (REPLACE (':4,:-9:TEST:TEST2:TEST3:', ':')))
                                     WHERE REGEXP_LIKE (r, '^[[:alpha:]]+');
                                    Output:
                                    R
                                    ------
                                    TEST
                                    TEST2
                                    TEST3
                                    Cheers,
                                    Manik.
                                    1 2 3 4 Previous Next