Home » SQL & PL/SQL » SQL & PL/SQL » char vs length() (10g 10.1.0.2.0 - xp)
char vs length() [message #398677] Fri, 17 April 2009 02:09 Go to next message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
my question is:

4 characters have been stored in a field. The Length function shows the length as 20. Why?
and how can I get the exact length of this field.
Please remember i m NOT using VARCHAR2 datatype.(in this scenario)




SQL> create table test
  2  (
  3  name char(20)
  4  )
  5  /

Table created.

SQL> insert into test values('MIKE');

1 row created.

SQL> select name,length(name) from test;

NAME                 LENGTH(NAME)
-------------------- ------------
MIKE                           20
SQL> 





Re: char vs length() [message #398679 is a reply to message #398677] Fri, 17 April 2009 02:16 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
because char datatype are always right padded with spaces. You should therefore user varchar2 datatype.
Re: char vs length() [message #398680 is a reply to message #398679] Fri, 17 April 2009 02:20 Go to previous messageGo to next message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
in my db all such datatypes are CHAR.(dmp file imported from old version). Then how can I handle the problem.

1. Should I change all such dataypes to VARCHAR2?
or
2. Is there any way to work around?

Re: char vs length() [message #398681 is a reply to message #398680] Fri, 17 April 2009 02:21 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
Quote:
2. Is there any way to work around?


workaround for what?
Re: char vs length() [message #398683 is a reply to message #398677] Fri, 17 April 2009 02:28 Go to previous messageGo to next message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
how to find exact length of the name in case of CHAR field?

I think I should use ltrim, rtrim combination to remove spaces and use length function after, to get the correct result. Am I right?

[Updated on: Fri, 17 April 2009 02:29]

Report message to a moderator

Re: char vs length() [message #398686 is a reply to message #398683] Fri, 17 April 2009 02:30 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
yes

select name,length(trim(name)) from test;
Re: char vs length() [message #398690 is a reply to message #398677] Fri, 17 April 2009 02:37 Go to previous messageGo to next message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
Thanks
Re: char vs length() [message #398702 is a reply to message #398690] Fri, 17 April 2009 03:33 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
But of course, the correct answer is what bonker said in his first reply:
convert these to varchar2

All your successors & peers will be eternally grateful for it.
Re: char vs length() [message #398763 is a reply to message #398677] Fri, 17 April 2009 08:25 Go to previous messageGo to next message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
You have been using Oracle a minimum of 4 years (based on the date you registered) and you still don't know the basics of Oracle such as CHAR and VARCHAR?
Re: char vs length() [message #398936 is a reply to message #398677] Sun, 19 April 2009 22:04 Go to previous message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
Dear Joy_Division

YES, I am PROUD to be a student(like you) of oracle and will be till my last day. and I have found this forum one of the BEST places to share things with GOOD people like YOU.

Can I ask you a question please?
Should I abandon to learn Oracle?
and what is this forum For?


Thanks
Previous Topic: Trigger with :new and RAISE_APPLIATION_ERROR
Next Topic: records in loop
Goto Forum:
  


Current Time: Thu Dec 08 00:35:24 CST 2016

Total time taken to generate the page: 0.17536 seconds