14 Replies Latest reply: Apr 2, 2013 1:01 AM by Most Wanted!!!! RSS

    How to use replace function to replace word

    user6287828
      I am trying to write a replace function in which I replace CA with California in an address string.

      I am trying to avoid using regular expression and just use other functions like replace, instr, trim etc.

      But what my main confusion is how do i take care of three cases in which.

      CA can be present with spaces before and after it like.

      - Redwood City, CA 96403

      CA can be present with spaces only before it and after it the string ends.

      - Redwood City, CA

      and I do not want to replace any other CA in the string that is part of other word

      - Blaca Street CA
      ( for example do not replace ca in Blaca)

      or

      - Cardinal Dr CA

      Thanks
        • 1. Re: How to use replace function to replace word
          Etbin
          Maybe replace(the_string,' CA',' California')

          Regards

          Etbin
          • 2. Re: How to use replace function to replace word
            user6287828
            yes I already tried that version

            but this version will not take of the third case I mentioned.

            that is if CA is present at the beginning of the string like

            3461 CALDVIN Dr CA

            it will make it

            3461 CaliforniaDVIN Dr California

            Edited by: user6287828 on Apr 1, 2013 12:56 PM

            Edited by: user6287828 on Apr 1, 2013 1:01 PM

            Edited by: user6287828 on Apr 1, 2013 1:01 PM
            • 3. Re: How to use replace function to replace word
              user6287828
              Ok any possible solution with REGEXP_REPLACE?
              • 4. Re: How to use replace function to replace word
                Frank Kulash
                Hi,

                Without using regular expressions:
                SELECT  RTRIM ( REPLACE ( addr_str || ' '
                                     , ' CA '
                               , ' California '
                               )
                           )     AS new_addr_str
                FROM    table_x
                ;
                Note that new_addr_str will never end with a space, even if the original addr_str did.
                If you need to preserve trailing spaces, then:
                SELECT  CASE
                         WHEN  SUBSTR (addr_str, -1) = ' '
                         THEN  REPLACE ( addr_str
                                       , ' CA '
                                 , ' California '
                                 )
                         ELSE  RTRIM ( REPLACE ( addr_str || ' '
                                              , ' CA '
                                      , ' California '
                                      )
                                     )
                     END     AS new_addr_str
                FROM    table_x
                ;
                Edited by: Frank Kulash on Apr 1, 2013 3:31 PM
                • 5. Re: How to use replace function to replace word
                  user6287828
                  Thanks but this does not work when CA is at the end. Because in that case CA does not have a space after it.
                  • 6. Re: How to use replace function to replace word
                    user6287828
                    This is part of a bigger problem where I will have to check for only few different states like

                    if CA replace it to California
                    or if NY replace it with New York

                    so may be if this is doable with REGEXP_REPLACE

                    I was trying something like


                    regexp_replace('string', ' CA$| NY$', ' replace string')


                    what should be this replace string and regular expression such that the corresponding 'CA|NY|NM' gets replaced by California or New York or New Mexico
                    but no other appearance of CA gets replaced whether in beginning or end of string

                    Thanks
                    • 7. Re: How to use replace function to replace word
                      ranit B
                      with xx as(
                          select 'Redwood City, CA 96403' a from dual UNION ALL
                          select 'CALDVIN Dr CA' a from dual UNION ALL
                          select 'Blaca Street CA' a from dual UNION ALL
                          select 'Redwood City, CA' a from dual 
                      )
                      select 
                          regexp_replace(a,'(\s)CA(\s)*','California') col 
                      from xx;
                      Output:
                      COL
                      Redwood City,California96403
                      CALDVIN DrCalifornia
                      Blaca StreetCalifornia
                      Redwood City,California
                      • 8. Re: How to use replace function to replace word
                        €$ħ₪
                        Is this?
                        WITH t
                             AS (SELECT 'Redwood City CA ' Col FROM DUAL
                                 UNION ALL
                                 SELECT 'Redwood City, CA USA' Col FROM DUAL
                                 UNION ALL
                                 SELECT 'Redwood City CAUSA' Col FROM DUAL)
                        SELECT CASE
                                  WHEN REGEXP_REPLACE (Col, '(.*) (CA)', '\2') IN ('CA','CA ') THEN col
                                  ELSE REGEXP_REPLACE (Col, 'CA', 'California')
                               END
                                  AS ColR
                          FROM T
                        • 9. Re: How to use replace function to replace word
                          Etbin
                          Sorry, didn't consider as your original post doesn't contain anything like *3461 CALDVIN Dr CA* (InitCaps assumed for anything but state abbreviations)
                          it will make it 3461 CaliforniaDVIN Dr California
                          You meant *3461 CaliforniaLDVIN Dr California* didn't you ? ;)

                          Regards

                          Etbin
                          • 10. Re: How to use replace function to replace word
                            Frank Kulash
                            Hi,
                            user6287828 wrote:
                            Thanks but this does not work when CA is at the end. Because in that case CA does not have a space after it.
                            It works when I do it.
                            SELECT  RTRIM ( REPLACE ( 'INCA TACATA CASA, CA' || ' '
                                                 , ' CA '
                                           , ' California '
                                           )
                                       )               AS new_addr_str
                            FROM    dual;
                            Output:
                            NEW_ADDR_STR
                            ----------------------------
                            INCA TACATA CASA, California
                            Post your query, along with CREATE TABLE and INSERT statements for a little sample data, and the results you want from that data.
                            See the forum FAQ {message:id=9360002}
                            • 11. Re: How to use replace function to replace word
                              Frank Kulash
                              Hi,
                              user6287828 wrote:
                              This is part of a bigger problem where I will have to check for only few different states like

                              if CA replace it to California
                              or if NY replace it with New York

                              so may be if this is doable with REGEXP_REPLACE
                              Regular expressions might not be the fastest way to do this, but the differenece might not be significant, or it might not justify the higher development and maintenance costs.

                              Even with regular expressions, if you need to replace any of N different abbreviations, you'll need N different REGEXP_REPLACE expressions. These might be nested, one inside another, or they might be done in series, sy with a MODEL or CONNECT BY clause.

                              If each string has (at most) 1 of those abbreviations, then a simpler soltuion is possible. That is, if the original string could have 'CA' or 'OR' or 'WA', but not 2 or all 3 of them together, then you could do it with only 1 REPLACE or REGEXP_REPLACE call.

                              In any case, post CREATE TABLE and INSERT statements for a little sample data, and the results you want from that data.
                              • 12. Re: How to use replace function to replace word
                                user6287828
                                Thanks for explaining Frank.

                                The original problem is with a small dataset of addresses where some of the abbreviations need to be replaced like I mentioned CA to be replaced by CALIFORNIA.
                                (only four of these abbreviations need to be replaced)

                                As these are abbreviations these will always be stand alone words and never part of words. And these abbreviation can be at the beginning or end or in between the string.
                                And any abbreviation can be present in one column.
                                More than one abbreviation can be present in one column
                                None of the abbreviations can be present in this column.

                                For example

                                column
                                3251 BLACA ROAD CA 94305
                                74 CALDWIN STREET CA
                                67 DIGITAL DRIVE NM
                                NM UNIVERSITY AVENUE 890
                                7645 ROCHESTER NY PARK STREET
                                834 GRAND AVENUE ATLANTA





                                output column
                                3251 BLACA ROAD CALIFORNIA 94305
                                74 CALDWIN STREET CALIFORNIA
                                67 DIGITAL DRIVE NEW MEXICO
                                NEW MEXICO UNIVERSITY AVENUE 890
                                7645 ROCHESTER NEW YORK PARK STREET
                                834 GRAND AVENUE ATLANTA

                                Thanks
                                • 13. Re: How to use replace function to replace word
                                  Frank Kulash
                                  Hi,
                                  user6287828 wrote:
                                  ... (only four of these abbreviations need to be replaced)
                                  In that case, nesting the functions one inside another won't be too bad. But watch out: this is exactly the kind of requirement that changes over time. You might only need 4 states now, nut next year they might expand the application so that you need all 50 states (plus Puerto Rico, and DC, and ....). In that case, something like MODEL, or a user-defined PL/SQL function, would be better.
                                  As these are abbreviations these will always be stand alone words and never part of words. And these abbreviation can be at the beginning or end or in between the string.
                                  And any abbreviation can be present in one column.
                                  More than one abbreviation can be present in one column
                                  None of the abbreviations can be present in this column.

                                  For example

                                  column
                                  3251 BLACA ROAD CA 94305
                                  74 CALDWIN STREET CA
                                  67 DIGITAL DRIVE NM
                                  NM UNIVERSITY AVENUE 890
                                  7645 ROCHESTER NY PARK STREET
                                  834 GRAND AVENUE ATLANTA
                                  Post CREATE TABLE and INSERT statements for the sample data.
                                  Include all special cases, e.g. more that one abbreviation in the same column.
                                  • 14. Re: How to use replace function to replace word
                                    Most Wanted!!!!
                                    WITH t AS
                                         (SELECT 'Redwood City, CA 96403' a
                                            FROM DUAL
                                          UNION ALL
                                          SELECT 'CALDVIN Dr CA' a
                                            FROM DUAL
                                          UNION ALL
                                          SELECT 'Blaca Street CA' a
                                            FROM DUAL
                                          UNION ALL
                                          SELECT 'Redwood City, CA' a
                                            FROM DUAL)
                                    SELECT trim(replace (REPLACE (a, ' CA ',' California '),' CA',' California ')) 
                                      FROM t
                                    regards,
                                    friend