Home » SQL & PL/SQL » SQL & PL/SQL » ALTER TABLE and MODIFY COLUMN
ALTER TABLE and MODIFY COLUMN [message #241755] Wed, 30 May 2007 10:00 Go to next message
e3339
Messages: 11
Registered: May 2007
Junior Member
Hello,

I need to modify a column in a table. The original column is
char(64). I need to modify it to varchar(60).

I ran the following two sql statements:

SELECT MAX(LENGTH(col1) FROM table1;
 answer was:64

SELECT COUNT(*) FROM table1
WHERE LENGTH(RTRIM(col1))>64
  answer was:0


So I have data in the column but no data past 60 characters. It must be white spaces. However my modify column command will not work it says I cannot make the column smaller than 64 characters as there is data in there.

Is there a way to trim the white spaces without having to create a second table and spool the data from table1 trim the values in col1 as I insert into table2?
Re: ALTER TABLE and MODIFY COLUMN [message #241770 is a reply to message #241755] Wed, 30 May 2007 10:32 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
create a table tab1
(col1 varchar2(60));

then
insert into tab1
select col1 from other table.

check wheather this does works or not.
Re: ALTER TABLE and MODIFY COLUMN [message #241774 is a reply to message #241755] Wed, 30 May 2007 10:37 Go to previous messageGo to next message
techno
Messages: 44
Registered: October 2003
Member
You can achieve that as follows

1. add a new column col2 of varchar2(60)
2. update the new column with original col values
update table1
set col2 = trim(column1);
3. drop the coulumn col1 through alter table command

And you can rename the col2 to col1 if required through alter table command



Re: ALTER TABLE and MODIFY COLUMN [message #241777 is a reply to message #241774] Wed, 30 May 2007 10:49 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
Here's an example:

SQL> CREATE TABLE t1 (c1 CHAR(62));

Table created.

SQL> INSERT INTO t1 VALUES ('qwerty');

1 row created.

SQL>
SQL> ALTER TABLE t1 ADD c2 VARCHAR2(60);

Table altered.

SQL> UPDATE t1 SET c2 = substr(c1,1,60);

1 row updated.

SQL> ALTER TABLE t1 DROP COLUMN c1;

Table altered.

SQL> ALTER TABLE t1 RENAME COLUMN c2 TO c1;

Table altered.

Re: ALTER TABLE and MODIFY COLUMN [message #242316 is a reply to message #241777] Fri, 01 June 2007 10:15 Go to previous messageGo to next message
e3339
Messages: 11
Registered: May 2007
Junior Member
Thank you for oyur replies. I decide to add another column, insert from the old column inot the new column. Then drop ld column and re-name new column.

But I have a new problem.

The old column is char(64), the new column is varchar2(60).

When I did the following on the old column:

SELECT COUNT(*) FROM table
WHERE LENGTH(RTRIM(old_column)>60;


The answer was: 0 rows

So that tells me anything beyond 60 chars is whitespaces.

So when I run:

UPDATE table 
SET new_column=RTRIM(old_column);


I get an error ora-01401 inserted vlaue too large for column.
I know there is nothing larger than 60 chars..

Any ideas?
Re: ALTER TABLE and MODIFY COLUMN [message #242317 is a reply to message #242316] Fri, 01 June 2007 10:24 Go to previous messageGo to next message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
Maybe it's left padded with spaces?
Re: ALTER TABLE and MODIFY COLUMN [message #242319 is a reply to message #242317] Fri, 01 June 2007 10:28 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
as you know that your column value is not greater than 6o then why not you using this command for insert


insert into my_table
values
(........,substr(col_name,1,60));

[Updated on: Fri, 01 June 2007 10:29]

Report message to a moderator

Re: ALTER TABLE and MODIFY COLUMN [message #242322 is a reply to message #242319] Fri, 01 June 2007 10:31 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
Have you noticed that your query was wrong?

Quote:
SELECT COUNT(*) FROM table1
WHERE LENGTH(RTRIM(col1))>64
answer was:0

Re: ALTER TABLE and MODIFY COLUMN [message #242323 is a reply to message #241755] Fri, 01 June 2007 10:36 Go to previous messageGo to next message
srmunnangi
Messages: 7
Registered: March 2005
Junior Member

Hi,

Try the following.

SELECT MAX (LENGTH (col1)) orignial_col_length
, MAX (LENGTH (LTRIM (RTRIM (co11)))) trim_col_length
FROM table1;


UPDATE table1
SET col1 = LTRIM (RTRIM (co11));

or else use

UPDATE table1
SET col1 = substr(col1,1,60)
WHERE LENGTH(col1)> 60 ;


ALTER TABLE table1 MODIFY (COL1 VARCHAR2(60));

[Updated on: Fri, 01 June 2007 10:39]

Report message to a moderator

Re: ALTER TABLE and MODIFY COLUMN [message #242326 is a reply to message #242322] Fri, 01 June 2007 10:38 Go to previous messageGo to next message
e3339
Messages: 11
Registered: May 2007
Junior Member
Hi,

Yes the querry is wrong. What I actually typed was:

SELECT COUNT(*) from tbale
where LENGTH(RTRIM(old_column)>60;


Answer: 0 rows

I also tried a substr command (old_column,1,60) and (old_column,1,59) did not work. Not sure why.
Re: ALTER TABLE and MODIFY COLUMN [message #242338 is a reply to message #242326] Fri, 01 June 2007 11:03 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Are both columns in bytes, not chars (multibyte-chars??)
Re: ALTER TABLE and MODIFY COLUMN [message #242349 is a reply to message #242338] Fri, 01 June 2007 11:15 Go to previous messageGo to next message
e3339
Messages: 11
Registered: May 2007
Junior Member
If I look at teh table via TOAD, it shows both columns as old_column(64 bytes) new_column(60 bytes). There as far as I know no double byte characters. They are jsut a description field in "English" There are commas, double qoutes and single qoutes.
Re: ALTER TABLE and MODIFY COLUMN [message #242360 is a reply to message #241755] Fri, 01 June 2007 12:07 Go to previous messageGo to next message
srmunnangi
Messages: 7
Registered: March 2005
Junior Member

Hi,

did you try like this! if so post the output result

UPDATE table1
SET col1 = substr(col1,1,60)
WHERE LENGTH(col1)> 60 ;


ALTER TABLE table1 MODIFY (COL1 VARCHAR2(60));

Re: ALTER TABLE and MODIFY COLUMN [message #242362 is a reply to message #242360] Fri, 01 June 2007 12:12 Go to previous messageGo to next message
e3339
Messages: 11
Registered: May 2007
Junior Member
  1  UPDATE standard_codes
  2  SET tmp_segment_abbrev=substr(segment_abbrev,1,60)
  3* WHERE LENGTH(segment_abbrev)>60
SQL> /

0 rows updated.


I then tried:

  1  UPDATE standard_codes
  2  SET tmp_segment_abbrev=substr(segment_abbrev,1,60)
  3* WHERE LENGTH(segment_abbrev)<60
SQL> /
UPDATE standard_codes
       *
ERROR at line 1:
ORA-01401: inserted value too large for column
Re: ALTER TABLE and MODIFY COLUMN [message #242368 is a reply to message #242362] Fri, 01 June 2007 12:33 Go to previous messageGo to next message
srmunnangi
Messages: 7
Registered: March 2005
Junior Member

Hi,

Try the following statements.

UPDATE standard_codes
SET tmp_segment_abbrev=substr(segment_abbrev,1,60)
WHERE ASCII (SUBSTR (segment_abbrev, 60, 1)) = 13;



ALTER TABLE standard_codes MODIFY (tmp_segment_abbrev VARCHAR2(60));
Re: ALTER TABLE and MODIFY COLUMN [message #242369 is a reply to message #241755] Fri, 01 June 2007 12:34 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
actually, I would do it like this

alter table my_table add column junk varchar2(60);

update my_table
set junk = substr(orig_column,1,60);

update my_table
set orig_column = null;

commit;

alter table my_table modify orig_column varchar2(60);

update my_table
set orig_column = junk;

commit;

alter table my_table drop column junk;


This will maintain the order of the columns so that any inserts without column lists will not blow up.

[Updated on: Fri, 01 June 2007 12:35]

Report message to a moderator

Re: ALTER TABLE and MODIFY COLUMN [message #242372 is a reply to message #242368] Fri, 01 June 2007 12:43 Go to previous messageGo to next message
e3339
Messages: 11
Registered: May 2007
Junior Member
I appologize. I typed the char length incorrecty. I should be 20 char long not 60. Does that change the ASII command?
Re: ALTER TABLE and MODIFY COLUMN [message #242378 is a reply to message #242369] Fri, 01 June 2007 14:06 Go to previous message
e3339
Messages: 11
Registered: May 2007
Junior Member
All thanks for the help. I finaly figured out what went wrong. There was a copywright sign in a single description field. I found it by running:

select segment_id, segment_key, segment_abbrev, length(substr(segment_abbrev,1,20)), lengthb(substr(segment_abbrev,1,20)) 
from standard_codes
where length(substr(segment_abbrev,1,20))<>lengthb(substr(segment_abbrev,1,20))



What was found was:
Casio® SL-300SV Handheld Solar Display Calculator


As soon as I ofund it I set the value to a single space and then my insert worked.
Previous Topic: how upadte statement works
Next Topic: show all recs from where clause
Goto Forum:
  


Current Time: Thu Dec 08 02:13:21 CST 2016

Total time taken to generate the page: 0.10812 seconds