This discussion is archived
1 2 Previous Next 29 Replies Latest reply: Jan 17, 2013 3:34 AM by Nicosa RSS

REGEXP_REPLACE > Need to isolate only a part of a string

985241 Newbie
Currently Being Moderated
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 Oracle ACE
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points