Solved

trouble in change attribute type from char to varchar2

Posted on 2002-06-06
2
974 Views
Last Modified: 2012-06-27
I create a table in oracle 8i and insert many data.

create table item(item_id number,
             location_id char(10));

Later on i changed the location_id from char(10) to varchar2(10)

using alter table modify(location_id varchar2(10))

But when i run the query

select item_id, location_id from item where location_id
= '114A';

I cannot select anything.

Actually there are many rows containing '114A' and it works fine before i make the change.

Please tell me what is wrong and how to fix it.
Thank you.

0
Comment
Question by:huhu
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 11

Expert Comment

by:pennnn
ID: 7059773
Maybe the reason is the difference between the CHAR and VARCHAR2 datatypes - the length of a CHAR(10) field is always 10, while the length of the VARCHAR2(10) field is the real length of the stored string - in your case ('114A') it would be 4.
So probably when your converted the datatype to VARCHAR2 all the strings got rightpadded with spaces.
If you are sure that your data doesn't contain trailing spaces you might try the following statement:
UPDATE item
SET location_id = RTRIM(location_id);

Then try to run your query again.
Hope that helps!
0
 
LVL 5

Accepted Solution

by:
kelfink earned 100 total points
ID: 7059798
When you stored the column as CHAR, oracle always padded the column out to the defined length, regardless of any extra spaces at the end.

insert into item values ( 1, '114A');
would have the identical effect as:
insert into item values ( 1, '114A ');


So when you converted to VARCHAR2(10),
oracle had two options... either automatically trim all spaces off the end of the columns, or leave the padding on all the rows.  They chose the simpler route, which is probably no more prone to error than if they had trimmed it for you.  After all, you can still trim it yourself, if desired, or let the padding stay and use LIKE.

The following two queries will both find the rows you want
select item_id, location_id from item where location_id
= '114A      ';
and
select item_id, location_id from item where rtrim(location_id) = '114A';

But you probably want to manually trim the columns yourself, to remove the extra padding:

UPDATE ITEM SET LOCATION_ID = rtrim(LOCATION_ID);





0

Featured Post

[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

635 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question