CODEWITHSUNDEEP
sqloracle-databasereplace
Aldo Tobing
Aldo Tobing

Reputation: 13

Replace a specific character in column names

I have two fields : 

-name
-address

How to change a specific letter? For example: 'a' changed to 'o' so the field names become:

-nome
-oddress

I've tried this but it doesn't work. Any help is appreciated.

Upvotes: 1

Views: 2927

Answers (3)

sebdes
sebdes

Reputation: 1

I had the same need where I had to replace " " with "" in more than a thousand field. I did it with Excel where I made concatenation in order to get a thousand different request like : alter table a rename column "a134 60 000" to "a134_____60_000"; Not very sexy... but solved in a couple of minutes!

Upvotes: 0

Trinimon
Trinimon

Reputation: 13957

This script replaces all A's in all column names of all tables belonging to owner SCOTT by O's:

DECLARE
  CURSOR alters IS
  SELECT 'ALTER TABLE ' || owner || '.' || table_name || ' RENAME COLUMN ' || 
          column_name ||' TO ' || REPLACE (column_name, 'A', 'O') AS statement
    FROM dba_tab_columns
   WHERE owner = 'SCOTT'
     AND column_name LIKE '%A%';
BEGIN
  FOR rec IN alters LOOP
    EXECUTE IMMEDIATE rec.statement;
  END LOOP;
END;

You need SELECT rights on table dba_tab_columns. Note that it is case-sensitive.

Have a lot of fun ;)

Upvotes: 5

kevinskio
kevinskio

Reputation: 4551

does this do what you want?

ALTER TABLE table_name
  RENAME COLUMN old_name to new_name;

Upvotes: 1

Related Questions