This discussion is archived
14 Replies Latest reply: Apr 1, 2013 11:01 PM by Most Wanted!!!! RSS

How to use replace function to replace word

user6287828 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    Maybe replace(the_string,' CA',' California')

    Regards

    Etbin
  • 2. Re: How to use replace function to replace word
    user6287828 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Ok any possible solution with REGEXP_REPLACE?
  • 4. Re: How to use replace function to replace word
    Frank Kulash Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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
    €$ħ₪ Expert
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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!!!! Journeyer
    Currently Being Moderated
    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

Legend

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