Home » SQL & PL/SQL » SQL & PL/SQL » How to reduce the column length (CHAR) ? [split topic TG]
How to reduce the column length (CHAR) ? [split topic TG] [message #438959] Wed, 13 January 2010 03:55 Go to next message
janmichael
Messages: 1
Registered: January 2010
Junior Member

I hope someone can help me
I like to decrease a column from 15 to 10

-- this is working
create table t ( col varchar2(14) ) ; -- create table
insert into t values ('12345678901234' ); -- insert data
alter table t modify col varchar2(10) ; -- not working jet

update t set col = substr(col,1,10); -- decrease data
select * from t; -- show the short data
alter table t modify col varchar2(10); -- working


-- but here it comes

create table c ( col char(15) ) ; -- create table - now with char (not varchar2 )
insert into c values ('123456789012345' ); -- insert data

alter table c modify col char(10) ; -- not working

update c set col = substr(col,1,10); -- decrease data

alter table c modify col char(10); -- and this is not working !!!!!!!!!!!!!!!!!!!


Why can i decrease a varchar but not a char ???
What can i do ?

(i am working with oracle 9 )

Re: How to reduce the column length (alternative way) ? [message #438963 is a reply to message #438959] Wed, 13 January 2010 04:06 Go to previous messageGo to next message
ThomasG
Messages: 3184
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Because a char is stored with a "fixed length", it's always stored padded with blanks up to it's length.

You have to change the column to varchar2 before you can make it smaller.

[Updated on: Wed, 13 January 2010 04:07]

Report message to a moderator

Re: How to reduce the column length (CHAR) ? [split topic TG] [message #438985 is a reply to message #438959] Wed, 13 January 2010 07:01 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
You can do it with just chars:
create table c ( col char(15) ) ;
insert into c values ('123456789012345' ); -- insert data 

alter table c add col2 char(10) ; -- add new column

update c set col2 = substr(col,1,10); -- copy data to new column

alter table c drop col; -- lose orignal column
alter table c add col char(10); -- re-add column with new size
update c set col = col2; -- copy data back
alter table c drop col2; --lose original column


That said, this is one example of the many reasons why char columns are a colossal pain to work with. If you can change them to varhcar2, do so.
Re: How to reduce the column length (CHAR) ? [split topic TG] [message #438987 is a reply to message #438985] Wed, 13 January 2010 07:06 Go to previous messageGo to next message
Michel Cadot
Messages: 63804
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why copying data when you can do it without any additional storage and table restructuration (that is row migrations)?

SQL> create table c ( col char(15) ) ;

Table created.

SQL> insert into c values ('123456789012345' ); 

1 row created.

SQL>  alter table c modify (col varchar2(15));

Table altered.

SQL> update c set col=substr(col,1,10);

1 row updated.

SQL> alter table c modify (col char(10));

Table altered.


Regards
Michel
Re: How to reduce the column length (CHAR) ? [split topic TG] [message #439034 is a reply to message #438987] Wed, 13 January 2010 12:30 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
SQL> update c set col=substr(col,1,10);

1 row updated.

SQL> alter table c modify (col char(10));

Table altered.

If the value in the col is not a constant or of 1 byte its always better to use varchar2

So, if i were I wont do the alter to modify to char again.

Thanks,
Ved
Re: How to reduce the column length (CHAR) ? [split topic TG] [message #439043 is a reply to message #439034] Wed, 13 January 2010 13:06 Go to previous messageGo to next message
Littlefoot
Messages: 20825
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Its_me_ved
So, if i were I ...

But, you ARE you! ./fa/5767/0/
Re: How to reduce the column length (CHAR) ? [split topic TG] [message #439053 is a reply to message #439043] Wed, 13 January 2010 14:39 Go to previous messageGo to next message
Michel Cadot
Messages: 63804
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Littlefoot wrote on Wed, 13 January 2010 20:06
Its_me_ved
So, if i were I ...

But, you ARE you! ./fa/5767/0/

Poor boy! Laughing

Re: How to reduce the column length (CHAR) ? [split topic TG] [message #439094 is a reply to message #439043] Thu, 14 January 2010 04:04 Go to previous messageGo to next message
pablolee
Messages: 2813
Registered: May 2007
Location: Scotland
Senior Member
Littlefoot wrote on Wed, 13 January 2010 19:06
Its_me_ved
So, if i were I ...

But, you ARE you! ./fa/5767/0/

Yeah, but He's not I, I'm I and he's he (Hee Hee Smile )
Re: How to reduce the column length (CHAR) ? [split topic TG] [message #439119 is a reply to message #439094] Thu, 14 January 2010 07:37 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
pablolee wrote on Thu, 14 January 2010 11:04
Littlefoot wrote on Wed, 13 January 2010 19:06
Its_me_ved
So, if i were I ...

But, you ARE you! ./fa/5767/0/

Yeah, but He's not I, I'm I and he's he (Hee Hee Smile )

hm...

wondering if pablolee is an alter ego of mine, since I too am I...
(I DO love Schotch.. not sure about Scots)

[Updated on: Thu, 14 January 2010 07:38]

Report message to a moderator

Re: How to reduce the column length (CHAR) ? [split topic TG] [message #439157 is a reply to message #438959] Thu, 14 January 2010 12:36 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Quote:

should be "If it was I" not I were I...


sorry,did not know that poeple over here now concentrating on english more than technical things!!! aaah.... it was a typo! but its funny to see the reaction of so many people in a technical forum like this? isn't it?? ...and still a sane individual with good understanding can understand what my reply was!!!!! I think I did not found one here!!!

hmmm..."sab pagal hain!!!" (all are mad!) was really a good suitable remark by a newbie on this forum...

yeah, i candid it was wrong while writing the statement but did not know people of your class would react more on some stupid stuff...
My advice you should also re read the forum guidelines once again!!!!!may be you are missing something or to lazy to read them.WHY SHOULD NOT YOU?






And ya, most of the time its being observed that ORAFAQ become more a forum to comment on stupid stuff deviating from original topic.

[Updated on: Thu, 14 January 2010 13:18]

Report message to a moderator

Re: How to reduce the column length (CHAR) ? [split topic TG] [message #439161 is a reply to message #439157] Thu, 14 January 2010 13:11 Go to previous messageGo to next message
pablolee
Messages: 2813
Registered: May 2007
Location: Scotland
Senior Member
Reel your neck in! We were having a little joke.
Re: How to reduce the column length (CHAR) ? [split topic TG] [message #439162 is a reply to message #439161] Thu, 14 January 2010 13:23 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
If you want to make joke , then I believe this is not the good place here..there is community hangout/General forum.
I would suggest make fun as much as you can...by posting link or whatever...

But doing such stuff just only deviates from the original question.It degrades the quality of solutions you people provide!! Hope the message I conveyed is clear here!!!



Regards,
Ved
Re: How to reduce the column length (CHAR) ? [split topic TG] [message #439164 is a reply to message #439162] Thu, 14 January 2010 13:37 Go to previous messageGo to next message
ThomasG
Messages: 3184
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Oracle begs to differ:

TNS-00000 : Not An Error
     Cause: Everything is working as it should.
    Action: Don't worry: Be happy.


Only the occasional joke makes live worth living, and *adds* to the quality of a solution.

Re: How to reduce the column length (CHAR) ? [split topic TG] [message #439165 is a reply to message #439164] Thu, 14 January 2010 13:50 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
ITS BEING SAID..."RULES ARE JUST TO BREAK.."
WOULD YOU LIKE TO DO THAT HERE? NO RIGHT?


[Updated on: Thu, 14 January 2010 13:53]

Report message to a moderator

Re: How to reduce the column length (CHAR) ? [split topic TG] [message #439166 is a reply to message #439165] Thu, 14 January 2010 14:07 Go to previous messageGo to next message
pablolee
Messages: 2813
Registered: May 2007
Location: Scotland
Senior Member
Laughing You realise that the more you post, the more ridiculous you appear, right? Let it lie, we were having a bit of fun, you have taken offence WAAAAAAAAaaaayyyyyyyy out of proportion, and all that is happening now is that you are projecting a very childish personality. Seriously, let it lie.
Re: How to reduce the column length (CHAR) ? [split topic TG] [message #439169 is a reply to message #439166] Thu, 14 January 2010 14:21 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Oh yeah, can see some more people here....
Laughing

Smile this is your third post here..dont the same thing apply to you as well? people of your class!!!!!

welcome!!!!
Re: How to reduce the column length (CHAR) ? [split topic TG] [message #439170 is a reply to message #439169] Thu, 14 January 2010 14:40 Go to previous messageGo to next message
pablolee
Messages: 2813
Registered: May 2007
Location: Scotland
Senior Member
Quote:
Oh yeah, can see some more people here....

No idea what you are attempting to say there.
Quote:
this is your third post here..dont the same thing apply to you as well?
What same thing? That I'm taking excessive ffence to a little bit of fun? No, I wouldn't say so.
Quote:
people of your class
And what class would that be?

Anyway, I find your ranting tiresome. goodbye.
Re: How to reduce the column length (CHAR) ? [split topic TG] [message #439227 is a reply to message #439170] Fri, 15 January 2010 01:38 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
pablolee wrote on Thu, 14 January 2010 21:40
Anyway, I find your ranting tiresome. goodbye.


Hm, apparently pablolee != I, because I find the ranting rather amusing.
Especially the part where it is said in three different replies that the topic is going off-topic!
Previous Topic: Server with two IP Address (SAN, LAN)
Next Topic: how to group and sum up certain fields
Goto Forum:
  


Current Time: Mon Sep 26 12:45:41 CDT 2016

Total time taken to generate the page: 0.19726 seconds