Results 1 to 3 of 3

Thread: Update a string

  1. #1
    Join Date
    Jul 2003
    Posts
    23

    Unanswered: Update a string

    So I have lots of address, the higher ups have decieded they don't want any abbreviations ... so Ave = Avenue St = Street is there a SQl statement that will allow me to do something like this? I could do it in vbscript if I wanted, but I would rather learn how to do it in sql server if possible.

    Thanks

    Damian
    "Everything is possible, somethings are just less likely then others"

  2. #2
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    You could use the REPLACE function.
    Code:
    set nocount on
    go
    
    create table #tmp ( Address varchar(100) )
    go
    insert #tmp values ('12345 John Ave. New York NY')
    insert #tmp values ('12345 John St. New York NY')
    insert #tmp values ('12345 John St New York NY')
    insert #tmp values ('12345 John Rd. New York NY')
    go
    select * from #tmp
    go
    update #tmp
    set Address = CASE
    		WHEN Address LIKE '% Ave. %' THEN REPLACE(Address, ' Ave. ', ' Avenue ')
    		WHEN Address LIKE '% St. %' THEN REPLACE(Address, ' St. ', ' Street ')
    		WHEN Address LIKE '% Rd. %' THEN REPLACE(Address, ' Rd. ', ' Road ')
    		WHEN Address LIKE '% Ave %' THEN REPLACE(Address, ' Ave ', ' Avenue ')
    		WHEN Address LIKE '% St %' THEN REPLACE(Address, ' St ', ' Street ')
    		WHEN Address LIKE '% Rd %' THEN REPLACE(Address, ' Rd ', ' Road ')
    		END
    
    select * from #tmp
    go
    drop table #tmp
    Output
    Address
    -----------------------------------
    12345 John Ave. New York NY
    12345 John St. New York NY
    12345 John St New York NY
    12345 John Rd. New York NY

    Address
    -----------------------------------
    12345 John Avenue New York NY
    12345 John Street New York NY
    12345 John Street New York NY
    12345 John Road New York NY


    NOTE:
    There could be some problems if the address had an 'St.' in it, example 1234 St. Clair St.
    MCDBA

  3. #3
    Join Date
    Jul 2003
    Location
    Penang, Malaysia
    Posts
    212
    I think you have to use the command "UPDATETEXT".
    But it only works if your column is of text,ntext and image type.

    Go to books online and search for for the explanation.
    Pretty blur reading through it the first time.

Posting Permissions

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