Clever Geek Handbook

Oracle length () function returns invalid value

I just found ... I don't know what I would call, 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 the length () call actually returns 1 + realLength values.

I'm not sure where to start, would anyone want to shed some light?

+2


source to share


2 answers


Try to view field details using 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 request

SELECT dump( 'Oracle' ) from dual
+5


source


You probably have an invisible character (like CR) at the end of those part_nums that TRIM () doesn't remove.

Just guess. :-)



Try to copy them with '[' || part_num || ']' in the selection and see if you notice any extra white space on either side of the field.

+3


source






More articles:


All Articles