sql - Function to replace words in a inputstring based on values in a table -

sql - Function to replace words in a inputstring based on values in a table -

hi,

i have table "tbl_address_replacement", attached screen shot of info in it.

my input string @input = 'auriga building 4'

i want replace above input string matching values in "word_contains" column in above table , replace matched values "replace_word" column value.

eg:

desired o/p : auriga bldg. 4

thankyou!

i wrote little while ago whilst doing scheme migration.

here's applicable code:

declare @rowcount int; -- repeat until there no iterations left (lazy recursive method) set @rowcount = 937; while (@rowcount > 0) begin update addresses set address_line_1 = replace(x.address_line_1, ' ' + y.word_contains + ' ', ' ' + y.replace_word + ' ') addresses x inner bring together tbl_address_replacement y on x.address_line_1 '% ' + y.word_contains + ' %' ; set @rowcount = @@rowcount; end ;

i found efficient method reduces number of iterations required.

basically keeps looping until update statement makes no more changes!

sql sql-server sql-server-2008 sql-server-2008-r2 sql-server-2012