CODEWITHSUNDEEP
oracle11gsql-update
mcquaim
mcquaim

Reputation: 179

Oracle - Update a Column length completely with #?

Sorry if this is a stupid question but I can't see an answer anywhere on the net so not sure if this is possible.

I am doing a project at the minute where I have to destroy client data and by destroy I mean make the client not identifiable.

I have many tables that I need to create a batch to update certain fields with the # character and several other just to blank them.

Is there any way to have an update statement where you can set the entire width of the column with # instead of specifying each one?

For instance, say on a table called CLIENT we have a NAME column declared as VARCHAR2(10). I am currently doing this:

UPDATE CLIENT C SET C.NAME = '##########' WHERE C.ID = xxxxxx;

Is there any way to do this where I don't have to specify 10 #'s, something like?

UPDATE CLIENT C SET C.NAME = ALL '#' WHERE C.ID = xxxxxx;

It is just that some fields are 4 characters, some 9, some 10, some 20 etc. If it is not possible then no big drama but I just thought it would be a more tidy approach if it could be done.

Thanks for the help, Mac

Upvotes: 0

Views: 57

Answers (2)

DB_learner
DB_learner

Reputation: 1026

You can use regexp_replace().

update client 
set name = regexp_replace(name,'[A-Za-z0-9]','#') 
where c.id = xxxxxx;

Upvotes: 1

Patrick Marchand
Patrick Marchand

Reputation: 3445

This could be one way to do it. This query will generate an update statement for all VARCHAR2 columns but you have to supply your own WHERE clause manually. 

select 'update '||table_name||' set '||
listagg(column_name||'= RPAD(''#'', '||data_length||', ''#'')', ', ') within group (order by column_name)
from all_tab_columns
where table_name = 'CLIENT'
and data_type = 'VARCHAR2'
group by table_name

Upvotes: 2

Related Questions