1 2 Previous Next 29 Replies Latest reply: Jan 17, 2013 3:34 AM by Nicosa-Oracle RSS

    REGEXP_REPLACE > Need to isolate only a part of a string

    985241
      Hi everyone,

      I hope someone will be able to help me with the construction of this REGEXP_REPLACE as I am on it since about 5 hours and I think I'm going crazy. :)

      So I have a field in my DB populated with many lines like this :

      BREAKFAST 15.00
      DINNER 30.00
      CITY TAX 1.50
      SPA 150.00


      This is an example.
      The field can be filled with this information, with more lines, less lines, can be empty.

      What I need to do is to isolate only the price of the CITY TAX. In this example, I need to only get the '1.50' string. If the field is empty or if I have no line with CITY TAX, I need to return 0.

      My try is as follows :
      I made only one line and suppressed the spaces like this :

      replace(replace(:RATE_PACKAGES, ' ', ''), chr(10), '')

      RATE_PACKAGES being the field of my DB which contain the information I need.

      With this 2 replaces I get : BREAKFAST15.00DINNER30.00CITYTAX1.50SPA150.00
      This is kinda ugly but it seemed the better way to do it to me. :(

      And after I made an only line with no spaces, there goes my REGEXP_REPLACE :

      regexp_replace(replace(replace(:RATE_PACKAGES, ' ', ''), chr(10), ''), '([[:graph:]]*)([CITYTAX])([[:graph:]]{4})([[:graph:]]*)', '\3')

      I used [[:graph:]]* to exclude all the printable characters of the string before CITYTAX and then [[:graph:]]{4} to isolate the 4 characters (1.50) following CITYTAX and finally [[:graph:]]* again to exclude all the printable characters of the string following the price of CITYTAX.
      I return only what I need with \3.

      This is working perfectly if CITY TAX is the last line in the field => '1.50'.
      If there is something after, like in the example above, it returns => 'PA15'

      I don't understand why. And perhaps there is a simple way to proceed ?

      I have tried several combinations in the REGEXP_REPLACE but I never get to get what I want. :(

      Edited by: 982238 on 16 janv. 2013 16:17
        • 1. Re: REGEXP_REPLACE > Need to isolate only a part of a string
          John Spencer
          I think you would be better off using regexp_substr to just pull out the bit you want instead of trying to null out everything except what you want.

          This seems to work after you "normalization" of the string:
          SQL> with sample_data as (
            2     select 'BREAKFAST15.00DINNER30.00CITYTAX1.50SPA150.00' str
            3     from dual union all
            4     select 'BREAKFAST15.00DINNER30.00SPA150.00CITYTAX1.50'
            5     from dual union all
            6     select 'CITYTAX1.50BREAKFAST15.00DINNER30.00SPA150.00'
            7     from dual union all
            8     select 'BREAKFAST15.00DINNER30.00SPA150.00'
            9     from dual)
           10  select str, regexp_substr(str, 'CITYTAX\d*\.?\d*') tax
           11  from sample_data;
          
          STR                                           TAX
          --------------------------------------------- -------------------------
          BREAKFAST15.00DINNER30.00CITYTAX1.50SPA150.00 CITYTAX1.50
          BREAKFAST15.00DINNER30.00SPA150.00CITYTAX1.50 CITYTAX1.50
          CITYTAX1.50BREAKFAST15.00DINNER30.00SPA150.00 CITYTAX1.50
          BREAKFAST15.00DINNER30.00SPA150.00
          Getting the 1.50 out of the result is left as an exercise :-)

          John
          • 2. Re: REGEXP_REPLACE > Need to isolate only a part of a string
            chris227
            No need to get rid of the line breaks:
            with data as (
            select
             'BREAKFAST 15.00
            DINNER 30.00
            CITY TAX 1.50
            SPA 150.00' str
            from dual
            )
            
            select
            regexp_substr(str,'CITY TAX (\d+(\.\d+)?)',1,1,null,1)
            str
            from data
            
            STR 
            1.50 
            
            If you are on an older version, which doesnt support the last parameter of regepx_substr
            
            select
            regexp_replace(str,'^.*CITY TAX (\d+(\.\d+)?).*','\1',1,1,'n')
            str
            from data
            
            STR 
            1.50 
            Edited by: chris227 on 16.01.2013 07:23
            dot masked

            Edited by: chris227 on 16.01.2013 07:26

            Edited by: chris227 on 16.01.2013 07:29

            Edited by: chris227 on 16.01.2013 07:42
            corrected cardinality of inner group
            • 3. Re: REGEXP_REPLACE > Need to isolate only a part of a string
              AlbertoFaenza
              Hi,

              check this example:
              WITH mydata(rate_packages) AS
              (
              SELECT 'BREAKFAST 15.00
              DINNER 30.00
              CITY TAX 1.50
              SPA 150.00'FROM DUAL UNION ALL
              SELECT 'BREAKFAST 10.00
              DINNER 40.00
              SPA 190.00'FROM DUAL UNION ALL
              SELECT '' FROM DUAL
              )
              SELECT NVL(REGEXP_SUBSTR(rate_packages,'CITY TAX +([0-9.]+)',1,1,NULL,1),'0') City_tax_rate
              FROM mydata;
              
              CITY_TAX_RATE                                        
              -----------------------------------------------------
              1.50                                                 
              0                                                    
              0         
              Regards.
              Al
              • 4. Re: REGEXP_REPLACE > Need to isolate only a part of a string
                chris227
                which one is more appropiate ;-?
                with data as (
                select
                 'BREAKFAST 15.00
                DINNER 30.00
                CITY TAX 1.5.0
                SPA 150.00' str
                from dual
                )
                
                select
                 regexp_substr(str,'CITY TAX (\d+(\.\d+)?)',1,1,null,1)
                str1
                ,REGEXP_SUBSTR(str,'CITY TAX +([0-9.]+)',1,1,NULL,1)
                str2
                from data
                
                STR1 STR2 
                1.5 1.5.0 
                OP will know ...
                • 5. Re: REGEXP_REPLACE > Need to isolate only a part of a string
                  985241
                  Thanks a lot to all of you for the many answers. :)

                  Unfortunatelly, none of your propositions are working for me. :(
                  Most of the time I get the error message "wrong number or types of arguments in call to 'REGEXP_SUBSTR'" with for example this try :

                  regexp_substr(:RATE_PACKAGES, 'CITYTAX +([0-9.]+)',1,1,NULL,1)

                  And with your proposition John, it returns blank. :(


                  I should perhaps precise that I'm creating a report, so I'm not using sqlplus or sql developper but creating a PL/SQL formula.
                  Maybe this is not working the same way ?

                  Edited by: 982238 on 16 janv. 2013 16:58
                  • 6. Re: REGEXP_REPLACE > Need to isolate only a part of a string
                    chris227
                    did you read thsi part of my post
                    If you are on an older version, which doesnt support the last parameter of regepx_substr
                     
                    select
                    regexp_replace(str,'^.*CITY TAX (\d+(\.\d+)?).*','\1',1,1,'n')
                    str
                    from data
                     
                    STR 
                    1.50
                    So it is advisable to post always the 4 digit oracle version.

                    Edited by: chris227 on 16.01.2013 08:00
                    • 7. Re: REGEXP_REPLACE > Need to isolate only a part of a string
                      AlbertoFaenza
                      982238 wrote:
                      Thanks a lot to all of you for the many answers. :)

                      Unfortunatelly, none of your propositions are working for me. :(
                      Most of the time I get the error message "wrong number or types of arguments in call to 'REGEXP_SUBSTR'" with for example this try :

                      regexp_substr(:RATE_PACKAGES, 'CITYTAX +([0-9.]+)',1,1,NULL,1)

                      And with your proposition John, it returns blank. :(


                      I should perhaps precise that I'm creating a report, so I'm not using sqlplus or sql developper but creating a PL/SQL formula.
                      Maybe this is not working the same way ?

                      Edited by: 982238 on 16 janv. 2013 16:58
                      Could you post your Oracle version?
                      SELECT * FROM v$version;
                      The use of subexpr parameter in REGEXP_SUBSTR has been introduced in 11g, if I'm not wrong.

                      Regards.
                      Al

                      Edited by: Alberto Faenza on Jan 16, 2013 5:00 PM
                      • 8. Re: REGEXP_REPLACE > Need to isolate only a part of a string
                        Nicosa-Oracle
                        Hi,

                        I would do something like that :
                        [11.2] Scott @ my11g > !cat t.sql
                        with t(f) as (
                        select 'BREAKFAST 15.00
                        DINNER 30.00
                        CITY TAX 1.50
                        SPA 150.00' f from dual
                        union all
                        select 'BREAKFAST 15.00
                        DINNER 30.00
                        SOMETHING ELSE 1.50
                        SPA 150.00' f from dual
                        )
                        ------ end of sample data ------
                        select
                                f
                                , case when instr(f,'CITY TAX')>0 then to_number(regexp_replace(f,'^.*CITY TAX ([.0-9]+).*$','\1',1,1,'n')) else 0 end ctax
                        from t;
                        
                        [11.2] Scott @ my11g > @t
                        
                        F                                   CTAX
                        ------------------------------ ---------
                        BREAKFAST 15.00                    1.500
                        DINNER 30.00
                        CITY TAX 1.50
                        SPA 150.00
                        
                        BREAKFAST 15.00                     .000
                        DINNER 30.00
                        SOMETHING ELSE 1.50
                        SPA 150.00
                        • 9. Re: REGEXP_REPLACE > Need to isolate only a part of a string
                          985241
                          Yes, I did. ;)
                          And I get the same errror message ("wrong number or types of arguments....").

                          @Nicosa : I'll try this tomorrow !

                          @All : Thanks again for your help ! I'll let you know tomorrow if there is some improvement. :)

                          Edited by: 982238 on 16 janv. 2013 17:03
                          • 10. Re: REGEXP_REPLACE > Need to isolate only a part of a string
                            AlbertoFaenza
                            chris227 wrote:
                            which one is more appropiate ;-?
                            Sorry Chris, we were posting more or less at the same time.
                            And I have added also cases with string NULL or not having CITY TAX.

                            Regards.
                            Al
                            • 11. Re: REGEXP_REPLACE > Need to isolate only a part of a string
                              Frank Kulash
                              Hi,
                              982238 wrote:
                              Unfortunatelly, none of your propositions are working for me. :(
                              Most of the time I get the error message "wrong number or types of arguments in call to 'REGEXP_SUBSTR'" with for example this try :

                              regexp_substr(:RATE_PACKAGES, 'CITYTAX +([0-9.]+)',1,1,NULL,1)
                              That sounds like you're using Oracle 10. The 6th argument to REGEXP_SUBSTR was a new feature in Oracle 11.1. If you want solutions that work in your version of Oracle, say what version of Orace that is.
                              This will wotk in Oracle 10.1 and higher:
                              ...     LTRIM ( REGEXP_REPLACE ( rate_packages
                                                          , '.*(CITY TAX [0-9.]+).*'
                                                          , '\1'
                                                    , 1
                                                    , 1
                                                    , 'n'
                                                          )
                                         , 'CITY TAX'
                                         )          
                              This assumes that 'CTIY TAX' occurs in rate_packages.
                              ... I should perhaps precise that I'm creating a report, so I'm not using sqlplus or sql developper but creating a PL/SQL formula.
                              Maybe this is not working the same way ?
                              That shouldn't matter. All the REGEXP functions are available in PL/SQL, apart from SQL.
                              Post your complete code, or a simplified version that has the same problem.
                              • 12. Re: REGEXP_REPLACE > Need to isolate only a part of a string
                                AlbertoFaenza
                                982238 wrote:
                                Yes, I did. ;)
                                And I get the same errror message ("wrong number or types of arguments....").

                                @Nicosa : I'll try this tomorrow !

                                @All : Thanks again for your help ! I'll let you know tomorrow if there is some improvement. :)

                                Edited by: 982238 on 16 janv. 2013 17:03
                                Post your full Oracle version
                                SELECT * FROM v$version;
                                Regards.
                                Al
                                • 13. Re: REGEXP_REPLACE > Need to isolate only a part of a string
                                  APC
                                  Let's cheat!
                                      select trim( replace ( your_column, 'CITY TAX', null) ) as amt
                                      from your_table
                                      where your_column like 'CITY TAX%';
                                  So when I proposed this approach I hadn't realised that all the data is actually in a single field (to be fair, I wasn't the only one to make that particular bloomer). But my solution still stands: we just need to tokenize the field somehow. But, given that the best way of achieving this would be with regex, it seems less pertinent: might as well just solve the whole thing with 'em.

                                  Cheers, APC

                                  Edited by: APC on Jan 17, 2013 9:37 AM
                                  • 14. Re: REGEXP_REPLACE > Need to isolate only a part of a string
                                    chris227
                                    I dont believe it:
                                    select * from v$version
                                    
                                    BANNER
                                    Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
                                    PL/SQL Release 10.2.0.5.0 - Production
                                    CORE     10.2.0.5.0     Production
                                    TNS for Solaris: Version 10.2.0.5.0 - Production
                                    NLSRTL Version 10.2.0.5.0 - Production
                                    
                                    declare
                                    l_val varchar2(100):=
                                     'BREAKFAST 15.00
                                    DINNER 30.00
                                    CITY TAX 1.50
                                    SPA 150.00';
                                    l_ret varchar2(50);
                                    begin
                                    l_ret:=regexp_replace(l_val,'^.*CITY TAX (\d+(\.\d+)?).*','\1',1,1,'n');
                                    DBMS_OUTPUT.PUT_LINE(l_ret);
                                    end;
                                    
                                    1.50
                                    1 2 Previous Next