OraFAQ Forum: General » char(1 byte) vs char(2 byte)

Home » Other » General » char(1 byte) vs char(2 byte) (Forms 6i , 10gR2)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

char(1 byte) vs char(2 byte) [message #564704]

Mon, 27 August 2012 12:49

helles_angel
Messages: 13
Registered: November 2011
Location: Edmonton, Alberta, Canada

Junior Member

Hi there,

I'm a little rusty with my data types and am hoping someone can help me out.

I'm increasing the length of a field from char(1 byte) to char(2 bytes) -this database field is visible and queryable from the front end as well. However, I need to update existing data in the table for the column accordingly so that users can still query for data effectively from the front end.

e.g. The values currently in the database for the particular column are 1, 2,..., 9. The client's requirements have dictated that they need values greater than 9. So, that means 10, 11...99. The kicker is that the length of the field is fixed as well at 2 characters in length (cannot be 1).

Why I don't just change the column to a varchar2 or number, I have no clue -it's a legacy system that I'm supporting so I'm just doing what I'm told lol

In any event, my question is, since the field length of is fixed, how would I update the data in the database so that users can query for values that are 1 character in length (i.e. values 1...9) given that the length of the field is fixed at 2? I performed the update and tried querying for values of 01 which yielded no results. I can't query for values with a single-character value (e.g. 1) since the fixed length is set to 2 characters.

I attempted the following and after I queried for records in the table, 1's were still displayed as 1's and not 01:

update TABLE001
set COLUMN001 = decode (COLUMN001, 
                        1, 01, 
                        2, 02, 
                        3, 03,
                        4, 04,
                        5, 05,
                        6, 06,
                        7, 07,
                        8, 08,
                        9, 09,
                        COLUMN001); --I even tried this update with single quotes around the values

The following works in TOAD:

select * 
from TABLE001 
where column001 = 01;

As does the following:

select * 
from TABLE001
where column001 = 1;

However, when I attempt to query for records via the front end of the application with the value of 01, no records are returned. When I query for records with the value of 1, records are returned.

How can I update the values in the database to forced the values of 1...9 to be 2 digits? Does that make sense?

If not, please let me know and I'll try to clarify.

Thanks for your time

[Updated on: Mon, 27 August 2012 12:54]

Report message to a moderator

Re: char(1 byte) vs char(2 byte) [message #564705 is a reply to message #564704]

Mon, 27 August 2012 12:58

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

get your datatype straight & do not rely upon implicit datatype conversion.
CHAR(2 BYTES) only stores "strings"; not "digits".
in Oracle strings are delimited by single quote marks; while numbers do not require single quote marks

>where column001 = 01; 
>where column001 = 1; 
above are both NUMBERS!
>where column001 = '1 ';
above might succeed or as below
>where column001 = ' 1';

[Updated on: Mon, 27 August 2012 12:59]

Report message to a moderator

Re: char(1 byte) vs char(2 byte) [message #564706 is a reply to message #564705]

Mon, 27 August 2012 13:12

helles_angel
Messages: 13
Registered: November 2011
Location: Edmonton, Alberta, Canada

Junior Member

Irk. OK, that makes sense. I was wondering why querying for records with a column value of 1, 01, or '1' worked whereas '01' did not.

String vs number. Gotcha. Been a while since I fussed around with chars. Everything is usually varchar2's lol

Thanks for your time.

Report message to a moderator

Previous Topic:	Oracle Server Response
Next Topic:	logging

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Nov 25 21:35:10 CST 2024