0

I am trying to add a temporary column to a table in order to fix leading and trailing spaces in the original column.

Original Column values (excluding speech marks):

" John Smith   "
"  Jay Morgan  "

Temporary column values (excluding speech marks):

"John Smith"
"Jay Morgan"

SQL:

alter table persons
add t_full_name as default trim(full_name);

However, this does not work. Am I trying to do something impossible?

Singh
  • 545
  • 2
  • 5
  • 24
  • What about this column makes it "temporary"? – Damien_The_Unbeliever Mar 28 '17 at 09:53
  • Sorry that was a typo, it is temporary because it needs to exist for a while getting its value from the original column as trimmed until I drop the original column and rename the temp column to original. – Singh Mar 28 '17 at 09:57
  • Did you search how to add a column? Where do you define the datatype, for example. – HoneyBadger Mar 28 '17 at 09:57
  • Yes, I know the syntax ALTER TABLE table_name ADD column_name datatype; I tried using VARCHAR2(255) instead of as - did not work either. – Singh Mar 28 '17 at 10:00
  • Is there a reason you don't want to just update the existing column values - performance? Can new rows be added after you add this new column? And are you adding a constraint on the new column to prevent leading/trailing spaces in future? It might be simpler to add a (permanent) virtual column and leave the padded column alone. – Alex Poole Mar 28 '17 at 11:39

3 Answers3

2

You can use a trigger instead of a function in the default value. Here an example:

CREATE TRIGGER setTrimValue BEFORE INSERT ON persons
FOR EACH ROW
BEGIN
   :new.t_full_name := TRIM(:OLD.full_name)
END;

Please check here: Use function as default value for column in Oracle11g

Restriction on Default Column Values A DEFAULT expression cannot contain references to PL/SQL functions or to other columns, the pseudocolumns CURRVAL, NEXTVAL, LEVEL, PRIOR, and ROWNUM, or date constants that are not fully specified.

Oracle Doc

Community
  • 1
  • 1
fingerprints
  • 2,751
  • 1
  • 25
  • 45
  • Thanks for the snippet of/link to the documentation, it really helps. – Singh Mar 28 '17 at 11:20
  • You can use `trim()` as that's a built-in function, not a PL/SQL function. But you can't refer to the `full_name` column, as the doc link also says. A trigger won't set the value for existing rows though. – Alex Poole Mar 28 '17 at 11:36
  • Hmm. I need the existing rows to be fixed as well. Anyways I have solved the issue now by actually updating the original column to trim(original_column). – Singh Mar 28 '17 at 13:36
1

This worked for me (Oracle 11g):

CREATE TABLE T2 (full_name VARCHAR2(40));
INSERT INTO T2 (full_name) VALUES ('  test  ');
SELECT * FROM T2;
ALTER TABLE T2 ADD (fu2 VARCHAR2(40) as (TRIM(full_name)));
SELECT * FROM T2;

Output

FULL_NAME   FU2
  test      test

Added after comment. If later you want to rename columns as you told, you can do the following:

ALTER TABLE T2 ADD fu3 VARCHAR2(40);
UPDATE T2 SET fu3=fu2;
ALTER TABLE T2 DROP COLUMN fu2;
ALTER TABLE T2 DROP COLUMN full_name;
ALTER TABLE T2 RENAME COLUMN fu3 TO full_name;

Or directly, if you don't want to do intermediate checks:

UPDATE T2 SET full_name=fu2;
ALTER TABLE T2 DROP COLUMN fu2;
etsa
  • 5,020
  • 1
  • 7
  • 18
  • 1
    This works, but it creates the temp column as a virtual column which will not allow me to rename/drop the original column as it used in the fu2 expression. – Singh Mar 28 '17 at 11:18
  • @Singh I added something to answer (I hope I understood your needs) – etsa Mar 28 '17 at 12:18
  • Yes, I saw it but there are too many operations happening now. I have solved the issue now using a procedure and updating all VARCHAR2 columns to trimmed version of the original column. – Singh Mar 28 '17 at 13:37
1

Add a virtual column:

ALTER TABLE PERSONS
ADD t_full_name GENERATED ALWAYS AS ( TRIM( BOTH FROM full_name ) );
MT0
  • 143,790
  • 11
  • 59
  • 117
  • I cannot use virtual column as it causes issues later when renaming the original column. – Singh Mar 28 '17 at 11:16