Cat4Storm Cat4Storm - 4 months ago 7
SQL Question

SQL - replace all column values with 'X' the same length

I am trying to create a stored procedure that replaces all values in one column with Xs the same length as the original values. Here is what I have so far:

SELECT REPLICATE('x', LEN(Name))


This code shows the output with Xs but it does not make this change permanently in the database. Is there a way to make this change permanently in the database?

Answer

You need to use an UPDATE statement to physically modify the record:

Update  YourTable
Set     Name = Replicate('x', Len(Name))

However, I would caution that this will update EVERY record in your table to just XXXX.... You will be effectively removing/destroying all data in that column.

Please do not run this statement unless you are absolutely certain this is what you intend to do.

If your goal actually is to remove all data from that column for every record, and your field is nullable, you could save some space by doing:

Update   YourTable
Set      Name = Null
Comments