Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2010
    Posts
    3

    Question Unanswered: Regular expression / Pattern matching

    I need to replace all abbrieviations to the whole words in an address column. So let's say "ST" in the following records must become "STREET" and not affecting "FIRST".

    123 FIRST ST
    123 FIRST ST APT100

    I tried the following, but none of them worked:

    update #tempaddress set address1 = replace(address1,' st',' street')
    where firstname like '% st$';

    update #tempaddress set address1 = replace(address1,' st',' street')
    where firstname like '% st *%';

    Can someone please correct my syntax?
    Much appreciated!

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Few points:
    • you're using standard like comparisons and not regular expressions at the moment so $ won't work like you expect.
    • how likely is the firstname to have the abbreviation "st"?
    • shouldn't all the abbreviations be in a table to cut down on the code
    • I've just come back from the pub so who knows.

  3. #3
    Join Date
    Mar 2010
    Posts
    3

    Question

    hi Mike. Thanks 4 ur response.
    it was my bad; I meant
    where address1 like '% st$';
    where address1 like '% st *%';

    not where firstname...

    Basically I wanted RD, ST, AVE, LN, CT to be converted into whole words in the address1 field.

  4. #4
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Code:
    update #tempaddress 
    set address1 = str_replace(address1,' st',' street')
    where address1 like '% st';
    
    update #tempaddress 
    set address1 = str_replace(address1,' st ',' street ')
    where address1 like '% st %';
    The above might work a bit better but the danger here is that you'll convert any occurrence of " st" to " street". If your address is "2 stan stevens st" then you'll end up with "2 streetan streetevens street" which isn't what you want. You'd do better looking at the patindex and stuff functions. You also need to decide what you want to ignore the case of the string and convert St and ST as well.

  5. #5
    Join Date
    Mar 2010
    Posts
    3

    Talking

    Hi Mike,
    thanks for your tips!
    I was able to update using the following codes. With the case where 'st' is not the end of the string, I include its next word (apt, suite, unit, #). Kinda doing repetitive work, but it worked out fine

    update #tempaddress
    set address1 = replace(address1,' st',' street')
    where rtrim(address1) like '% st';

    update #tempaddress
    set address1 = replace(address1,' st ',' street ')
    where address1 like '% st apt%';

    Thanks a lot for your helps again!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •