[Webinar] Learn how to a build a cloud-first strategyRegister Now


update field inside table

Posted on 2014-08-01
Medium Priority
Last Modified: 2014-08-01
Hi, i have one table with some fields that were hacked and all ntext and varchar fields were added some text inside. I need to update all rows and fields that have this text to take out the text and leave the rest inside.

lets say that the table name is mytable and I have a lots of fields, so i need some query to get the fields name automatic and then go over all rows and check if the field have the text lets say "hacked text", then i need to take out only the part hacked text inside the fields
Question by:rafaelrgl
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 1000 total points
ID: 40234579
UPDATE mytable
your_column1 = REPLACE(your_column1, 'hacked text', '') , 
your_column2 = REPLACE(your_column2, 'hacked text', '') 
repeat_for_each_column = REPLACE(repeat_for_each_column, 'hacked text', '') 

Open in new window

This assumes that 'hacked text' wasn't in there in the first place.  If it was, no way around that one, unless the hacked text is always the prefix/suffix/anything you can define..
LVL 41

Accepted Solution

Kyle Abrahams earned 1000 total points
ID: 40234584
This will generate Jim's sample with individual updates.  

Run this query, copy the result set into management studio and let fly.
select 'update ' + cast(table_catalog as varchar(255)) + '.' 
+ cast(table_schema as varchar(25)) + '.'
+ cast(table_name as varchar(255)) + ' set [' +
cast(column_name as varchar(255)) + '] = replace([' + cast(column_name as varchar(255)) +
'],''this is your hacked text'', ''''); '
where DATA_TYPE in('varchar','nvarchar')

Open in new window


Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question