sql server - Query to find and replace a specific word in SQL Database without affecting the data along with it -

sql server - Query to find and replace a specific word in SQL Database without affecting the data along with it -

i have db table holds entries info of industries people belong primary industry , secondary industry :

for ex : aerospace , defense,retail, wholesale , distribution research , development,technology, media , telecommunications etc.

there around 500 such entries. want separating symbol ',' between primary industry , secondary industry , in industry name.

for ex. : aerospace , defense,retail, wholesale , distribution

should

aerospace , defense,retail wholesale , distribution (without comma)

is there anyway can achieved , ie replacing comma secondary term without affecting primary term.

first of all, have wrong table design. single column should never list bunch of values separated commas. nightmare deal (as discovering).

instead, there should separate table linking people industries, allowing multiple rows per person:

person_id industry 1 retail, wholesale , distribution 1 aerospace , defense 2 retail, wholesale , distribution

then bring together table whenever need find industries person or grouping of people.

obviously, makes comma problem go away.

of course, stuck dumb table design. if so, come replacement solution using replace() (thanks other answers mentioning replace function).

update:

here working sqlfiddle solution:

updated utilize replace():

update bad_design set bad_design.industries = replace(bad_design.industries, from_str, to_str) ( select * bad_design bring together replacements on replace(bad_design.industries, from_str, to_str) <> '' ) a.person_id = bad_design.person_id;

note: won't perform multiple replacements on same string in 1 pass. need more that, such recursive query.

sql sql-server sql-server-2008