Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2003
    Posts
    4

    Question Unanswered: Replacing Multiple rows with searched multiple values

    I have two tables..

    Table1.Field1 Values ("North Highway", "Green Street", "Blue Road", "Pink Lane")

    Table1.Field1 Values (" Road", " Lane", " Street", " Highway")
    Table2.Field2 Values (" Rd.", " Ln.", " St.", " Hwy.")

    I would like to create a function or a sp_ that searches Table1.Field1 for the values in Table2.Field1 AND replaces it with Table2.Field2

    Possible?
    How?

    It would of course be possible with a
    replace(replace(replace(Table1.Field1," Road"," Rd.")," Lane"," Ln.")," Street"," St.")

    But friends, I dont think I need to mention that this is purely an example..
    Table2 contains about 260 records.

    thnx.

    Marcus

  2. #2
    Join Date
    May 2003
    Location
    Parsippany NJ
    Posts
    36

    Wink

    you dont need to create a function.BTW,you provided incorrect info.
    Table1.Field1 Values (" Road", " Lane", " Street", " Highway")
    Table2.Field2 Values (" Rd.", " Ln.", " St.", " Hwy.")

    should be
    Table2.Field1 Values (" Road", " Lane", " Street", " Highway")
    Table2.Field2 Values (" Rd.", " Ln.", " St.", " Hwy.")

    Here is the query

    update table1 set field1 =
    substring(field1,1,charindex(' ',field1)-1)+
    (case when right(field1,len(field1)-charindex(' ',field1))= 'Highway' then ' Hwy.'
    when right(field1,len(field1)-charindex(' ',field1))= 'Street' then ' St.'
    when right(field1,len(field1)-charindex(' ',field1))= 'Lane' then ' Ln.'
    when right(field1,len(field1)-charindex(' ',field1))= 'Road' then ' Rd.' end) from table1

Posting Permissions

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