matches and like

I am using IDS 11.50 and just noticed an odd thing with matches and like. Querying a table with a character field containing values shorter than the field length like "xxxx " will not match matches "* *" or match like "% %". They will match "* " and "% " though. Might this be a bug?

Can you proved a test case? I can imagine a few explanations, but it all depends on the datatypes, query plans and so on... On Fri, Oct 4, 2013 at 6:35 PM, TOM LOCKE <tomlocke@countyofplumas.com>wrote: > I am using IDS 11.50 and just noticed an odd thing with matches and like. > Querying a table with a character field containing values shorter than the > field length like "xxxx " will not match matches "* *" or match like "% %". > They will match "* " and "% " though. Might this be a bug? > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --001a11c32e88e349b504e7edcb45

Here is the table create: create table cc_dist ( dist_code char(7) not null ) When I run this query I get all the codes: select dist_code from cc_dist dist_code TAX PEN INT EOP MOD CDF TRUST HOLD TAX PEN INT EOP MOD CDF TRUST HOLD When I run these queries I get nothing: select dist_code from cc_dist where dist_code matches "* *" select dist_code from cc_dist where dist_code like "% %"

The ANSI standard has special requirements for char columns concerning space padding, for this reason I do not believe you are going to get the result you are looking for. John F. Miller III STSM, Lead Architect miller3@us.ibm.com 503-747-1366 IBM Informix Dynamic Server (IDS) ids-bounces@iiug.org wrote on 10/04/2013 12:34:43 PM: > From: "TOM LOCKE" <tomlocke@countyofplumas.com> > To: ids@iiug.org, > Date: 10/04/2013 12:35 PM > Subject: Re: matches and like [31615] > Sent by: ids-bounces@iiug.org > > Here is the table create: > > create table cc_dist > (> > dist_code char(7) not null > ) > > When I run this query I get all the codes: > > select dist_code > from cc_dist> > dist_code > TAX > PEN > INT > EOP > MOD > CDF > TRUST > HOLD > TAX > PEN > INT > EOP > MOD > CDF > TRUST > HOLD > > When I run these queries I get nothing: > > select dist_code > from cc_dist > where dist_code matches "* *"> > select dist_code > from cc_dist > where dist_code like "% %"> > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

I'm not sure exactly why you would expect those patterns to match... And to be honest I hate questions and issues around this topic... Simply because it usually comes down to ANSI standard compliance... And the ANSI standard is not freely distributable - although it may be found in some dark corner of the Internet - and it's clearly not easy to read... Let's see. CHAR(n) columns are stored with "n" characters. If you specify less than "n" it will be padded. If you use leading spaces they'll be ignored, as they'll be indistinguishable from the padded ones. VARCHARs(n) are stored with as much as "n" characters long, and if you specify leading spaces they'll be kept. But for comparisons, the leading spaces are ignored, because if L1 and L2 (length of both strings being compared) are not equal, the smaller one will be padded. As such... A "% %" will only match a string that has some characters, then a space, and then some characters like "John Doe" or "John Doe". This applies to both CHAR and VARCHAR. The formal justification would need proper quoting of the standard and believe me... it would be very complex. Having said this, feel free to argument... But at this moment I see no bug. Naturally a formal IBM response could be obtained with a PMR. Regards On Fri, Oct 4, 2013 at 8:34 PM, TOM LOCKE <tomlocke@countyofplumas.com>wrote: > Here is the table create: > > create table cc_dist > (> > dist_code char(7) not null > ) > > When I run this query I get all the codes: > > select dist_code > from cc_dist> > dist_code > TAX > PEN > INT > EOP > MOD > CDF > TRUST > HOLD > TAX > PEN > INT > EOP > MOD > CDF > TRUST > HOLD > > When I run these queries I get nothing: > > select dist_code > from cc_dist > where dist_code matches "* *"> > select dist_code > from cc_dist > where dist_code like "% %"> > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --001a11c18e7af1a13504e7f28aba