=?windows-1252?Q?store_data_as_=91regular_expression=92_and_pass_a_li?= =?windows-1252?Q?teral_in_the_sql?=

Ever store the data as ‘regular expressions’ and pass a literal in the sql? Example: I can’t do this: Store StringField as “_SD%” in TableName, then use sql like this: Select * from TableName where StringField LIKE “ASDF”; Returns nothing I *can* do this: Store StringField as “ASDF” in TableName, then use sql like this: Select * from TableName where StringField LIKE “_SD%” This should return records with StringField Values with “SD” in the 2nd and 3rd position of the string [1 and 2 for zero based folks] - simple enough Why doesn’t this work both ways ? AM I missing something fairy obvious ? More specific sample: create temp table tab1 ( x char(10), y integer ) with no log; insert into tab1 values("_SD%", 5); These statements: select * from tab1 where x like ("_S%"); -- returns result or select * from tab1 where x like ("ASDF"); -- does not returnresult don't work. Is there a way to do a "reverse match" like that?

tenesmus wrote: > Ever store the data as ‘regular expressions’ and pass a literal in the > sql? > > Example: > > I can’t do this: > > Store StringField as “_SD%” in TableName, then use sql like this: > Select * from TableName where StringField LIKE “ASDF”;> > Returns nothing > > I *can* do this: > > Store StringField as “ASDF” in TableName, then use sql like this: > Select * from TableName where StringField LIKE “_SD%”> > This should return records with StringField Values with “SD” in the > 2nd and 3rd position of the string [1 and 2 for zero based folks] - > simple enough > > Why doesn’t this work both ways ? > AM I missing something fairy obvious ? > > More specific sample: > > create temp table tab1 > ( > x char(10), > y integer > ) with no log;> > insert into tab1 values("_SD%", 5);> > These statements: > > select * from tab1 where x like ("_S%"); -- returns result> > or > > select * from tab1 where x like ("ASDF"); -- does not return> result > > don't work. > > Is there a way to do a "reverse match" like that? > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list the LIKE operator takes the string to be compared as the left operand and the pattern as the right operand, so select * from tab1 where "ASFD" like x does what you want, duh! -- Ciao, Marco ______________________________________________________________________________ Marco Greco /UK /IBM Standard disclaimers apply! Structured Query Scripting Language http://www.4glworks.com/sqsl.htm 4glworks http://www.4glworks.com Informix on Linux http://www.4glworks.com/ifmxlinux.htm

Thanks Marco. I accept your “duh” with dignity, and I deserve it. ~ for me, sometimes the most obvious stuff is overlooked. Just for the record, your example does not work because of the typo.

tenesmus wrote: > Thanks Marco. > > I accept your 'duh' with dignity, and I deserve it. > > ~ for me, sometimes the most obvious stuff is overlooked. > > Just for the record, your example does not work because of the typo. Duh! :o) -- Cheers, Obnoxio The Clown http://obotheclown.blogspot.com -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.

Reverse the operands WHERE ... "ASDF" LIKE StringField....: > create table regex(one char(10)); Table created. > insert into regex values ("A%%"); 1 row(s) inserted. > insert into regex values ("A_"); 1 row(s) inserted. > select * from regex where "All" like one; one A%% 1 row(s) retrieved. > select * from regex where "BAll" like one; one No rows found. Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Sep 30, 2009 at 12:21 PM, tenesmus <mail@lamepage.com> wrote: > Ever store the data as ‘regular expressions’ and pass a literal in the > sql? > > Example: > > I can’t do this: > > Store StringField as “_SD%” in TableName, then use sql like this: > Select * from TableName where StringField LIKE “ASDF”;> > Returns nothing > > I *can* do this: > > Store StringField as “ASDF” in TableName, then use sql like this: > Select * from TableName where StringField LIKE “_SD%”> > This should return records with StringField Values with “SD” in the > 2nd and 3rd position of the string [1 and 2 for zero based folks] - > simple enough > > Why doesn’t this work both ways ? > AM I missing something fairy obvious ? > > More specific sample: > > create temp table tab1 > ( > x char(10), > y integer > ) with no log;> > insert into tab1 values("_SD%", 5);> > These statements: > > select * from tab1 where x like ("_S%"); -- returns result> > or > > select * from tab1 where x like ("ASDF"); -- does not return> result > > don't work. > > Is there a way to do a "reverse match" like that? > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list >