Oracle length vs SQL Server len

Oracle returns only the length of contents only. please check that ur datafield is of char type and containing some spaces

See attached.
comments.txt

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.

select dump(your_column ) from your table where ....;
give me the output
 

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 ..

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.

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

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.

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.

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

varchar2 datatype doesn't store trailing spaces...plz do not use char data type as franck mentioned

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.

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.

i agree with franck, plz use rtrim instead of trim as leading space may be meaning full or required in certain cases

See attached.
comments.txt