Thread: char_length()?
char_length()/octet_length() for char() datatype returns a character length *except* the trailing blanks. Is this what the standard expects? Oracle's length() returns 3 in the case below. test=> create table t2 (c char(3)); CREATE test=> insert into t2 values('c'); INSERT 277385 1 test=> select * from t2; c -----c (1 row) test=> select char_length(c) from t2;length -------- 1 (1 row) test=> select octet_length(c) from t2;octet_length -------------- 1 (1 row) -- Tatsuo Ishii
Tatsuo Ishii <t-ishii@sra.co.jp> writes: > char_length()/octet_length() for char() datatype returns a character > length *except* the trailing blanks. Is this what the standard > expects? Oracle's length() returns 3 in the case below. AFAICT Oracle is right --- the spec just says i) If the data type of S is a character data type, then the result is the number of charactersin the value of S. and I can't see anything there about stripping pad characters. You could ask for length(trim(S)) if you don't want to count blanks. regards, tom lane
> Tatsuo Ishii <t-ishii@sra.co.jp> writes: > > char_length()/octet_length() for char() datatype returns a character > > length *except* the trailing blanks. Is this what the standard > > expects? Oracle's length() returns 3 in the case below. > > AFAICT Oracle is right --- the spec just says > > i) If the data type of S is a character data type, then the > result is the number of characters in the value of S. > > and I can't see anything there about stripping pad characters. You > could ask for length(trim(S)) if you don't want to count blanks. Ok, I have committed fixes for this. Maybe we should add this incompatible changes to the release note: char_length()/octet_length() for the char() datatype now returns a character length (or byte length) including the trailing blanks. -- Tatsuo Ishii