none
Record Update Issue in SQL

    Question

  • Hello,

    I have thousands of records in SQL database and one column is there with the name "Address" which contains the values like "'L12 West St" or "S12 East St".

    Here , i want to update the character "St" to "Street".

    When i am trying to do this, it is also updating "East" to "EaStreet". or "West" to "WeStreet"

    But i want to update only character "St" to "Street".

    How i can do this ?

     please respond me.

    Thanks!!!

    • Moved by Tom PhillipsModerator Thursday, December 06, 2012 8:20 PM TSQL question (From:SQL Server Database Engine)
    Thursday, December 06, 2012 7:13 PM

Answers

  • The most complete solution is likely to create a CLR procedure and handle regular expressions there.  A somewhat hacky workaround for this case might be something such as the following sample:

    --drop table names2
    create table names2(foo varchar(50))
    
    insert into names2
    select '123 fake st'
    union all
    select '123 east g ave'
    union all
    select '123 fake st sw'
    union all
    select '123 west alameda st'
    union all
    select '123 east west st'
    
    UPDATE names2
    SET foo = REPLACE(foo, ' st', ' Street')
    WHERE foo like '% st'
    OR foo like '% st %'
    
    select foo from names2

    Thanks,
    Sam Lester (MSFT)


    My Blog

    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

    • Marked as answer by K.S-1637 Thursday, December 06, 2012 8:03 PM
    Thursday, December 06, 2012 7:46 PM
    Moderator

All replies

  • The most complete solution is likely to create a CLR procedure and handle regular expressions there.  A somewhat hacky workaround for this case might be something such as the following sample:

    --drop table names2
    create table names2(foo varchar(50))
    
    insert into names2
    select '123 fake st'
    union all
    select '123 east g ave'
    union all
    select '123 fake st sw'
    union all
    select '123 west alameda st'
    union all
    select '123 east west st'
    
    UPDATE names2
    SET foo = REPLACE(foo, ' st', ' Street')
    WHERE foo like '% st'
    OR foo like '% st %'
    
    select foo from names2

    Thanks,
    Sam Lester (MSFT)


    My Blog

    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

    • Marked as answer by K.S-1637 Thursday, December 06, 2012 8:03 PM
    Thursday, December 06, 2012 7:46 PM
    Moderator
  • >> I have thousands of records [sic: rows are not record] in SQL database and one column is there with the name 'Address' [sic: too generic for ISO-11179 rules] which contains the values like 'L12 West St' or 'S12 East St'. Here , I want to update 'St' to 'Street'. <<

    I HOPE NOT! In your “careful research” did you ever hear of CASS?? It is the format for street addresses required by the USPS. You can get tools that will format your address data to CASS Standards. 



    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Thursday, December 06, 2012 10:34 PM