1 2 Previous Next 29 Replies Latest reply: Jan 17, 2013 5:34 AM by Nicosa-Oracle Go to original post RSS
      • 15. Re: REGEXP_REPLACE > Need to isolate only a part of a string
        chris227
        Oh, thanks for the news, we hadnt that yet.
        • 16. Re: REGEXP_REPLACE > Need to isolate only a part of a string
          985241
          Woah !
          Thanks a lot for your help everyone.

          The version of Oracle :

          09:17:13 SQL> select * from v$version;

          BANNER
          ----------------------------------------------------------------
          Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
          PL/SQL Release 10.2.0.3.0 - Production
          CORE 10.2.0.3.0 Production
          TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
          NLSRTL Version 10.2.0.3.0 - Production

          I'll try your propositions and will let you know. :)
          • 17. Re: REGEXP_REPLACE > Need to isolate only a part of a string
            985241
            Frank Kulash wrote:
            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.
            Hi Frank,

            This is not working either. :(
            It returns : "Y TAX 1.50"


            Actually, the example I gave you was simplified and translated. ^^
            The sample I'm working with currently only contains the following "Taxe de Séjour 1.50".
            With your query transposed for my case, it returns "éjour 1.50".

            Here is your query transposed :
            ltrim(regexp_replace(:RATE_PACKAGES, '.*(Taxe de Séjour [0-9.]+).*', '\1', 1, 1, 'n'), 'Taxe de Séjour')
            And here is all my PL/SQL formula :
            function CF_CUST_RATEFormula return Char is
            
            begin
              
              if :MULTIPLE_RATE_INFO is null then
                   return :ROOM_REVENUE || ' ' || :CURRENCY_CODE;
                   
              else return ltrim(regexp_replace(:RATE_PACKAGES, '.*(Taxe de Séjour [0-9.]+).*', '\1', 1, 1, 'n'), 'Taxe de Séjour');
                
              end if;
              
            end;
            Maybe the "é" is causing trouble ?
            I'll try without this special character...

            Edited by: Aphelios_ on 17 janv. 2013 10:14

            --> OK I tried without the special character and it works fine when there is only "Taxe de Sejour" in my field.
            When there are more lines, it returns everything. Even if I make an only line like this :
            ltrim(regexp_replace(replace(replace(:RATE_PACKAGES, ' '), chr(10)), '.*(TaxedeSejour [0-9.]+).*', '\1', 1, 1, 'n'), 'TaxedeSejour')
            So I need a way to make it works with the special character and several lines. :)

            Edited by: Aphelios_ on 17 janv. 2013 10:19
            • 18. Re: REGEXP_REPLACE > Need to isolate only a part of a string
              chris227
              I dont see any sense in this ltrim
              You may try the same as i already did post, just replace the character class \d with [0-9]. May be this was unknown in (soon unsupported) 10.2.0.3. No, it is in the docs. Unclear why OP says former solution didnt work on 10.2.3.
              REGEXP_REPLACE ( rate_packages
                                          , '^.*CITY TAX ([0-9.]+).*'
                                          , '\1'
                                    , 1
                                    , 1
                                    , 'n'
                                          )
              or
              
              REGEXP_REPLACE ( rate_packages
                                          , '^.*Taxe de Séjour ([0-9.]+).*'
                                          , '\1'
                                    , 1
                                    , 1
                                    , 'n'
                                          )
              
               
               
               
              Edited by: chris227 on 17.01.2013 01:35
              • 19. Re: REGEXP_REPLACE > Need to isolate only a part of a string
                AlbertoFaenza
                Hi,

                are you sure this one is not working?
                WITH mydata(rate_packages) AS
                (
                SELECT 'BREAKFAST 15.00
                DINNER 30.00
                Taxe de Séjour 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(LTRIM(REGEXP_SUBSTR(rate_packages,'Taxe de Séjour +([0-9.]+)',1,1,NULL),'Taxe de Séjour '),0) City_tax_rate
                FROM mydata;
                
                CITY_TAX_RATE                                              
                -----------------------------------------------------------
                1.50                                                       
                0                                                          
                0            
                Regards.
                Al
                • 20. Re: REGEXP_REPLACE > Need to isolate only a part of a string
                  Frank Kulash
                  Hi,
                  Aphelios_ wrote:
                  ... This is not working either. :(
                  It returns : "Y TAX 1.50"


                  Actually, the example I gave you was simplified and translated. ^^
                  The sample I'm working with currently only contains the following "Taxe de Séjour 1.50".
                  With your query transposed for my case, it returns "éjour 1.50".

                  Here is your query transposed :
                  ltrim(regexp_replace(:RATE_PACKAGES, '.*(Taxe de Séjour [0-9.]+).*', '\1', 1, 1, 'n'), 'Taxe de Séjour')
                  And here is all my PL/SQL formula :
                  function CF_CUST_RATEFormula return Char is
                  
                  begin
                  
                  if :MULTIPLE_RATE_INFO is null then
                       return :ROOM_REVENUE || ' ' || :CURRENCY_CODE;
                       
                  else return ltrim(regexp_replace(:RATE_PACKAGES, '.*(Taxe de Séjour [0-9.]+).*', '\1', 1, 1, 'n'), 'Taxe de Séjour');
                  
                  end if;
                  
                  end;
                  Maybe the "é" is causing trouble ?
                  Yes, it looks like LTRIM doesn't handle the accented é the way we want it to.

                  You can remove 'Taxe de Séjour ' using REPLACE or SUBSTR, or using regular expressions.
                  For example:
                  ELSE RETURN REPLACE ( REGEXP_REPLACE ( :RATE_PACKAGES
                                                          , '.*(Taxe de Séjour [0-9.]+).*'
                                           , '\1'
                                           , 1
                                           , 1
                                           , 'n'
                                           )
                                , 'Taxe de Séjour '
                                );
                  If you use REGEXP_REPLACE instead of REGEXP_SUBSTR, then it removes 'Taxe de Séjour '. The problem with REGEXP_REPLACE is that, if 'Taxe de Séjour ' does not occur in the string, then REGEXP_REPLACE returns the string unchanged, and you probably want something that returns NULL.
                  • 21. Re: REGEXP_REPLACE > Need to isolate only a part of a string
                    chris227
                    Aphelios_ wrote:
                    When there are more lines, it returns everything. Even if I make an only line like this :
                    ltrim(regexp_replace(replace(replace(:RATE_PACKAGES, ' '), chr(10)), '.*(TaxedeSejour [0-9.]+).*', '\1', 1, 1, 'n'), 'TaxedeSejour')
                    So I need a way to make it works with the special character and several lines. :)

                    Edited by: Aphelios_ on 17 janv. 2013 10:19
                    This is easy, you left the space in the regexp expression

                    This is obviuosly an advantage of Albertos solution with regexp_substr, that it returns nothing, if no match is found, instead of the whole string in same case with regexp_replace.
                    I just would use replace instead of ltrim.
                    NVL(replace(REGEXP_SUBSTR(rate_packages,'Taxe de Séjour +([0-9.]+)',1,1,NULL),'Taxe de Séjour '),0)
                    Edited by: chris227 on 17.01.2013 01:40

                    Edited by: chris227 on 17.01.2013 01:47
                    • 22. Re: REGEXP_REPLACE > Need to isolate only a part of a string
                      chris227
                      >
                      I chose LTRIM rather than REPLACE or SUBSTR because, if you had multiplle spaces after 'CITY TAX',
                      How that, there is only a match with one space with your regexp?

                      Why not just take a solution with just the number in the grouping expression as already posted several times?

                      Edited by: chris227 on 17.01.2013 01:42
                      • 23. Re: REGEXP_REPLACE > Need to isolate only a part of a string
                        Nicosa-Oracle
                        Hi,

                        Did you try what I proposed ?
                        {message:id=10796517}
                        (...)
                        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
                        (...)
                        • 24. Re: REGEXP_REPLACE > Need to isolate only a part of a string
                          jeneesh
                          OffTopic: May I know how are you providing a link to your particular message?
                          • 25. Re: REGEXP_REPLACE > Need to isolate only a part of a string
                            _Karthick_
                            You can get the message ID of a specific post from the address bar when you click reply for that post and then you can just use it like this

                            {noformat}
                            {message:id=10798097}
                            {noformat}

                            and you will get

                            {message:id=10798097}
                            • 26. Re: REGEXP_REPLACE > Need to isolate only a part of a string
                              Nicosa-Oracle
                              jeneesh wrote:
                              OffTopic: May I know how are you providing a link to your particular message?
                              Sure, you write it like that :
                              {noformat}{message:id=10798097}{noformat}
                              You can easily find the messageid from the link on the little warning symbol on the upper right of each message (right click on the warning symbol, choose "copy link location", paste it and keep only the last part which is the messageid.)
                              • 27. Re: REGEXP_REPLACE > Need to isolate only a part of a string
                                jeneesh
                                Thanks, Karthic and Nicosa..
                                • 28. Re: REGEXP_REPLACE > Need to isolate only a part of a string
                                  985241
                                  Well, finally I get it to work with all of your help ! :)

                                  Thanks a lot !

                                  Here is the finale code of my PL/SQL formula and it works lkike a charm :
                                  FUNCTION CF_CUST_RATEFormula
                                    RETURN CHAR
                                  IS
                                    ts_price NUMBER := COALESCE(to_number(ltrim(regexp_substr(REPLACE(:RATE_PACKAGES, 'é', 'e'),'Taxe de Sejour +([0-9.]+)', 1, 1, NULL), 'Taxe de Sejour '), '99,999,999.99'), 0) * COALESCE(:NO_OF_ADULTS, 0);
                                  BEGIN
                                    IF :MULTIPLE_RATE_INFO IS NULL THEN
                                      RETURN :ROOM_REVENUE || ' ' || :CURRENCY_CODE;
                                    ELSE
                                      RETURN COALESCE(to_number(:room_rate, '99,999,999.99'), 0) - COALESCE(ts_price, 0) || ' ' || :CURRENCY_CODE;
                                    END IF;
                                    
                                    EXCEPTION WHEN OTHERS THEN RETURN 'ERREUR';
                                  END;
                                  I was a bit annoyed that on my test server I had to convert the string to UTF8 on the fly (convert(:RATE_PACKAGES, 'UTF8', 'WE8ISO8859P1')) in order to be able to replace the "é" but on the production server I didn't encounter the problem.

                                  Thanks a lot again for the activity on this thread. :)

                                  @nicosa : I did not allow much time to your proposal as it seemed to me a bit difficult to apply in my PL/SQL code. Sorry. :(
                                  • 29. Re: REGEXP_REPLACE > Need to isolate only a part of a string
                                    Nicosa-Oracle
                                    Aphelios_ wrote:
                                    @nicosa : I did not allow much time to your proposal as it seemed to me a bit difficult to apply in my PL/SQL code. Sorry. :(
                                    (o_0)

                                    That is as difficult/easy as every other proposal :
                                    (...)
                                    yourReturnVariable  := case when instr(f,'CITY TAX')>0 then to_number(regexp_replace(f,'^.*CITY TAX ([.0-9]+).*$','\1',1,1,'n')) else 0 end ;
                                    (...)
                                    You would only had to replace f with your variable.

                                    Anyway, glad you find a solution.
                                    :-)
                                    1 2 Previous Next