Post reply

Replace Ave with Avenue

  • October 21, 2010 at 9:51 am

    #388069

    Hi,

    I have a table which consists of a column named Address

    I am supposed to replace in the column, the word Ave to Avenue.

    I gave as below

    update empdetails set address = replace(replace(address,'Ave ','Avenue '),'Ave. ','Avenue ')

    But the word Ave comes with the below possiblities.

    1. Ave Maria Street

    2. 12 Ave.

    3. 12 ave,

    I want the ave to be replaced to avenue

    But i should also ensure that the words like

    eg 12 Musgrave , -- there is word ave in the final part of the string.

    Here the ave should not be replaced.

    Suggestions pls

  • October 21, 2010 at 10:27 am

    #1240455

    you need to test for the space before ave as well to avoid substrings of a street name:

    you are already testing for a space after the {Ave} or {Ave.}, so adding the preceeding space should get you where you want.

    update empdetails set address = replace(replace(address,' Ave ',' Avenue '),' Ave. ',' Avenue ')

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply