update query for a value in every or selected columns

[mcnewsxp]

lets say i want to replace the value "DEV" in any column that might contain that string with null.
how to?

tia,
mcnewsxp

[Bob Barrows]

Post by mcnewsxp
lets say i want to replace the value "DEV" in any column that might
contain that string with null. how to?

First of all, you cannot replace the value with NULL unless "DEV" is the
entire content of that column in that row. If you're talking about looking
for substrings ("DevAppServer", "DevBackend"), and you don't want to lose
the remaining characters, you need to replace it with an empty string ('')
so that you get "AppServer" and "Backend" after the replacement.

SQL does not provide a way to to easily perform a search-and-replace on all
columns in all tables. You will need to loop through all the tables,
querying the syscolumns view to get all the columns with character
datatypes, then generating dynamic sql statements to perform your updates.

A client tool such as ADO might make this a little simpler, but there is
still no avoiding the need for nested loops even using that route.

An alternative might be to export the data to Excel, perform the
search-and-replace, truncate the table, and import the revised data back
into the table.

[mcnewsxp]

mcnewsxp wrote: > lets say i want to replace the value "DEV" in any column that might > contain that string with null. how to? > First of all, you cannot replace the value with NULL unless "DEV" is the entire content of that column in that row. If you're talking about looking for substrings ("DevAppServer", "DevBackend"), and you don't want to lose the remaining characters, you need to replace it with an empty string ('') so that you get "AppServer" and "Backend" after the replacement. SQL does not provide a way to to easily perform a search-and-replace on all columns in all tables. You will need to loop through all the tables, querying the syscolumns view to get all the columns with character datatypes, then generating dynamic sql statements to perform your updates. A client tool such as ADO might make this a little simpler, but there is still no avoiding the need for nested loops even using that route. An alternative might be to export the data to Excel, perform the search-and-replace, truncate the table, and import the revised data back into the table.

"DEV" is all i am looking for.
thanks for your help. i know what to do now.