Looping Replace SQL SERVER 2005
Posted on 2008-10-22
I have a table 't_companies' and a table 't_thesaurus'. t_companies has 'id', 'company_name', 'new_company_name' columns. t_thesaurus has 'find_string', 'replace_string'.
What I'd like to do is replace all instances of a word in t_thesaurus.find_string with the 'replace_string'.
t_companies.company name = 'smith bros and co'
t_thesaurus.find_string has two records that could fix the company name, 'bros' and 'co', to be replaced by values of 'brothers' and 'company', respectively.
in the end, i'd like t_companies.new_company_name = 'smith brothers and company'.
any easy ideas? i'm using sql server 2005. it would be great to be able to do in SSIS, but i don't have any idea the complexity of SSIS vs T-SQL.