Oracle length() function returning incorrect value.

Question

I just found some... I don't know what I'd call it but here goes:

SELECT part_num, 
       length(trim(part_num)) 
  FROM part_programs 
 WHERE rownum <= 10;

...results:

PART_NUM        LENGTH(TRIM(PART_NUM))
--------------- ----------------------
THAB256         8
THA1256674      11
THA1256674GU    13
THA1257141      11
THA1257141FR    13
THA1257141FR1   14
THA1257141TD    13
THA2002013      11
THA2002013MI    13
THA2002013MI1   14

The returned integer from length() call actually returns 1 + realLength of the values.

I'm not sure where to begin, anyone care to shed a light?

Answer 1

You probably have a non-visible character (like a CR) on the end of those part_nums that TRIM() does not remove.

Just a guess. :-)

Try bracketing them with '[' || part_num || ']' in the select and see if you notice some extra white-space on either side of the field.

Ron

Answer 2

Try looking at the detail of the field by using the built in DUMP function

SELECT part_num,
       length(trim(part_num)),
       dump( trim( part_num ) )
  FROM part_programs  
 WHERE rownum <= 10;

This will return data like

Typ=96 Len=6: 79,114,97,99,108,101

from this query

SELECT dump( 'Oracle' ) from dual