Обсуждение: CHAR LIKE VARCHAR
Hi! I came across following difference between "LIKE" and "=" regarding CHARs and VARCHARs create table aa(f5 char(5), fv varchar(5)); insert into aa values('str1', 'str1'); select count(*) from aa where f5 = fv; > 1 select count(*) from aa where f5 like fv; > 0 I understand trailing spaces in CHAR are not significant though I expect DBMS shows consistent (and, ideally, clearly documented) behavior. From my point of view in example above it would be nice to have the same result for both queries regardless it is 0 or 1. Of course, I may be wrong. Is there a clear concept behind the difference between "LIKE" and "="? Thanks. -- Best regards Ilja Golshtein
Ilja Golshtein wrote: > Hi! > > I came across following difference > between "LIKE" and "=" regarding > CHARs and VARCHARs > > create table aa(f5 char(5), fv varchar(5)); > insert into aa values('str1', 'str1'); > select count(*) from aa where f5 = fv; > >>1 > > select count(*) from aa where f5 like fv; > >>0 > > I understand trailing spaces in CHAR are not > significant though I expect DBMS shows > consistent (and, ideally, clearly documented) > behavior. If you ask me (and it's too late to back out now :-) the whole behaviour of CHAR(n) is wrong, broken and just a bad idea. >From my point of view in example above > it would be nice to have the same result > for both queries regardless it is 0 or 1. > > Of course, I may be wrong. Is there a clear > concept behind the difference between "LIKE" > and "="? Well, you could argue that LIKE should ignore the trailing spaces (and the only way to decide is to look at the SQL specs). The problem is, that if f5=fv then presumably length(f5)=length(fv) and length(f5 || '+') = length(f5)+1 Of course, only the first of these is true because the whole idea of char(n) is badly thought out. Either the value contains spaces or it doesn't - unfortunately it's neither and both. -- Richard Huxton Archonet Ltd
On Jun 7, 2005, at 6:03 PM, Richard Huxton wrote: > If you ask me (and it's too late to back out now :-) the whole > behaviour of CHAR(n) is wrong, broken and just a bad idea. Just a quick addition: AFAIK, this bad, broken behavior (I definitely agree!) is per SQL spec. :( Don't know about the LIKE operator, however. Michael Glaesemann grzm myrealbox com
Michael Glaesemann <grzm@myrealbox.com> writes: > On Jun 7, 2005, at 6:03 PM, Richard Huxton wrote: >> If you ask me (and it's too late to back out now :-) the whole >> behaviour of CHAR(n) is wrong, broken and just a bad idea. > Just a quick addition: AFAIK, this bad, broken behavior (I definitely > agree!) is per SQL spec. :( Don't know about the LIKE operator, > however. AFAICS the behavior of LIKE is per spec also. I quote from SQL92: b) The <predicate> M LIKE P is true if there exists a partitioning of M into substrings such that: i) A substring of M is a sequence of 0 or more contiguous <character representation>s of M and each <character repre- sentation> of M is part of exactly one substring. ii) If the i-th substring specifier of P is an arbitrary char- acter specifier, the i-th substring of M is any single <character representation>. iii) If the i-th substring specifier of P is an arbitrary string specifier, then the i-th substring of M is any sequence of 0 or more <character representation>s. iv) If the i-th substring specifier of P is neither an arbi- trary character specifier nor an arbitrary string speci- fier, then the i-th substring of M is equal to that sub- string specifier according to the collating sequence of the <like predicate>, without the appending of <space> ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ characters to M, and has the same length as that substring ^^^^^^^^^^^^^^^ specifier. v) The number of substrings of M is equal to the number of substring specifiers of P. Under ordinary CHAR rules we could consider '1 ' as the substring matching the final '1' of the pattern, so that 's' 't' 'r' '1 ' would match the pattern 's' 't' 'r' '1', but the underlined part of rule 8.5.5.b.iv appears to specifically forbid that for LIKE. Which is too bad, because it would be easy to "fix" this to behave more sanely --- just remove the bpcharlike function and associated ~~ operator, so that the case would be handled by converting CHAR(n) to text (and thereby stripping trailing spaces). The bottom line is indeed that CHAR() is a nasty, useless, misdesigned datatype. Use text or varchar. regards, tom lane