[selander]

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

[ClaireHsu]

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