• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1728
  • Last Modified:

Oracle length vs SQL Server len

In SQL Server if you want to know the lengh of fields in a column you will use len(columnname).  In my SQL Server database if there is nothing in the field so then len(columnname) = 0.  With the same database but in Oracle you would think it would be the same with length(columnname).  This is giving me the length of the default column length, 8 but not the fields inside the column.
0
Littleghostface
Asked:
Littleghostface
  • 7
  • 3
  • 2
  • +2
1 Solution
 
Shaju KumbalathCommented:
Oracle returns only the length of contents only. please check that ur datafield is of char type and containing some spaces
0
 
awking00Commented:
See attached.
comments.txt
0
 
LittleghostfaceAuthor Commented:
In both my oracle and sql server database my there is no content in the in the database field.  The fields are char(8).  When the contents is null, sql server returns 0 while oracle returns 8.  Please advise.
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
Shaju KumbalathCommented:
select dump(your_column ) from your table where ....;
give me the output
 
0
 
shru_0409Commented:
LENGTH calculates length using characters as defined by the input character set.
in oracle see the example......

LENGTH (NULL) ==> NULL
LENGTH ('') ==> NULL -- Same as a NULL string.
LENGTH ('abcd') ==> 4
LENGTH ('abcd ') ==> 5

u can use like this

select case when len(columnname)  is null then 0 else len(columnname) end ..
0
 
LittleghostfaceAuthor Commented:
You can not use len in Oracle.  In Oracle my field is char(8).  I updated a field with 'AAA'.  So when I do length(columname) it should return 3 for that field and not 8.
0
 
Shaju KumbalathCommented:
it should return 3 only, so we need to check why it is return 8;
so please use dump function on the filed instead of length function so that we can diagonse the problem
0
 
Franck PachotCommented:
Hi,

In Oracle:
 - CHAR datatype is padded with spaces to its maximum length. You should always use VARCHAR2, not CHAR
 - zero length string is null. So nothing returns a length of 0. LENGTH('') returns null

So I suppose you can have the same behaviour with: nvl(length(rtrim(column)),0)

Regards,
Franck.
0
 
Shaju KumbalathCommented:
in oracle char column datatype it stores trailing spaces ,
so plz use
select length(trim(col)) from table
0
 
LittleghostfaceAuthor Commented:
Ok I got it.  Oracle calculates the blank spaces in the field where SQL Server doesn't.  So since the field was char(8) it was giving me a length of 8.  So you have to trim it first then length.  length(trim(column)) gives me the correct length count.
0
 
Shaju KumbalathCommented:
if u see the dump use can see  something like this Len=8: 64,64,64,32,32,32,32,32 something like this... 32 means there are 5 spaces padded with ur columns data
0
 
Shaju KumbalathCommented:
varchar2 datatype doesn't store trailing spaces...plz do not use char data type as franck mentioned
0
 
Franck PachotCommented:
Hi,
You should use rtrim or you 'll have a different result when you have leading spaces
You should use nvl if you want a length of 0 for empty strings
Regards,
Franck.
0
 
shru_0409Commented:
column_name    CHAR (8); --Always a full 8 characters!


You will rarely need or want to use the CHAR datatype in Oracle-based applications. In fact, it's recommended that you never use CHAR unless there is a specific requirement for fixed-length strings or unless you are working with data sources like DB2. Character data in DB2 is almost always stored in fixed-length format due to performance problems associated with variable-length storage. So, if you build applications that are based on DB2, you may have to take fixed-length data into account in your SQL statements and in your procedural code. You may, for example, need to use RTRIM to remove trailing spaces from (or RPAD to pad spaces onto) many of your variables in order to allow string comparisons to function properly.


0
 
Shaju KumbalathCommented:
i agree with franck, plz use rtrim instead of trim as leading space may be meaning full or required in certain cases
0
 
awking00Commented:
See attached.
comments.txt
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 7
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now