Re: NLS SQL syntax

The two queries are asking quite different questions. The operands of the MATCHES operator can be shown as: lhs MATCHES rhs The pattern is always the right-hand side (rhs) of the operator. The subject string is always the left-hand side (lhs) of the operator. >From: "Leonid Belov" <white@intek.spb.su> >Date: 18 Sep 1996 16:05:16 +0400 >X-Informix-List-Id: <news.28179> > >Hello Informixers, > >who can tell me why Informix executes the next two statements > in a different way: > > SELECT something FROM somewhere WHERE proc() MATCHES "*text*" This statement compares the output of proc() with the constant pattern "*text*" and the WHERE clause evaluates to TRUE if the string returned by proc() contains the characters 'text' somewhere. > SELECT something FROM somewhere WHERE "*text*" MATCHES proc(). By contrast, this compares the literal string '*text*' with the pattern returned by proc(). Unless the string returned by proc() is a pattern which will match those 6 characters, the test will fail -- it will fail most of the time, because if proc() returns 'this is a text string' as the pattern, then the subject string '*text*' does not match that pattern (though 'this is a text string' as the subject string does match the pattern '*text*'). In general: (lhs MATCHES rhs) DOES NOT IMPLY (rhs MATCHES lhs) >when proc() returns single NCHAR string containing national characters. >It seems to me that second statement works correctly while first >returns wrong (but stable) sequence of rows. > >Both statemets work correctly when proc() replaced by name of NCHAR column. > >I use Informix-Online 7.12 on Sparc Solaris 2.4. Thanks for including the version information. Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>