update field inside table

Posted on 2014-08-01
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 65

    Assisted Solution

    by:Jim Horn
    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 39

    Accepted Solution

    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


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    This is my first ever Article on EE or elsewhere; therefore, please bear with me if I have some discrepancies in my writing. I read many articles and questions related to "how to pass values to SSIS packages at run-time?"  Hence, this common ques…
    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.

    760 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

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now