RE: LIKE and Regular Expressions AKA regex in SQL

Theo, Is this documented or valid for release 12.x? I cannot find it in either of the Informix Guides to SQL. Still, I will test the syntax and see if it flies. ------------------------------ Jacob Salomon --- Nobody goes there anymore, it's too crowded. --Attr: Yogi Berra ------------------------------

https://www.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.dbext.doc/ids_dbxt_544.htm ------------------------------ Andreas Legner ------------------------------

Hi again, Theo. The [[:digit:]] syntax failed with: (URXA4) - regex compile error: invalid character class But the [0-9] syntax worked. Thanks MUCH, Theo. Art suggested: select tabname from mytables where tabname matches 'frag*[0-9]';I don't think that will match multiple digits at the end of the string. Thanks all! I have my solution and I'm a trifle smarter. But I have the 12.10 Datablades API Function Reference Manual IFO me and a search for REGEX (within the PDF) does not find it. So where is this delightful datablade documented? ------------------------------ Jacob Salomon --- Nobody goes there anymore, it's too crowded. --Attr: Yogi Berra ------------------------------

Calling the REGEX_MATCH() function will cause the engine to auto-install the regex datablade that supplies the function. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. ------Original Message------ Theo, Is this documented or valid for release 12.x? I cannot find it in either of the Informix Guides to SQL. Still, I will test the syntax and see if it flies. ------------------------------ Jacob Salomon --- Nobody goes there anymore, it's too crowded. --Attr: Yogi Berra ------------------------------ #Informix

Art cautioned: > Calling the REGEX_MATCH() function will cause the engine to auto-install the regex datablade that supplies the function. I wonder who will notice that? I added it to my FragmentList.pm Perl module (not yet on iiug, I think) but afterward got my hands on the latest Extensions guide (2016, thanks, Andreas) and there are SO MANY caveats on its use that I thought I would have to revert to the clumsier "^sbstest%" construct. Or add so much intelligence to the module that it can tell is the regex blade is usable and then decide what clause to include in the SQL. Fortunately I read a bit closer. Server requirement: The Scheduler must be running. (When is it not running? Question for another thread.) I'm playing with simple strings so the issue of CLOB and and SBspace is irrelevant for me. Database requirements: Database MUST be logged. (Most are..) Since my utility feeds off sysmaster, this is not a problem. Database MUST NOT be ANSI. Same lucky break in my case. I think I've had exactly one client who uses ANSI mode for an Informix database. ------------------------------ Jacob Salomon --- Nobody goes there anymore, it's too crowded. --Attr: Yogi Berra ------------------------------

Hello Jacob, I tested the regex function by manually installing the blade module. -- step1. creating database $ echo "create database testdb" | dbaccess - Database created. Database closed. -- step2. installing blade module for testdb database $ blademgrol_informix1210_1>show databases Databases on server: stores_demo sysuser testdb ol_informix1210_1>list testdb There are no modules registered in database testdb. ol_informix1210_1>show modules 16 DataBlade modules installed on server ol_informix1210_1: LLD.1.20.FC2 Node.2.0 c TSAFuncs.1.00.FC1 TSPIndex.1.00.FC1 TSPMatch.2.00.FC1 TimeSeries.6.00.FC7 binaryudt.1.0 bts.3.10 excompat.1.0 ifxbuiltins.1.1 ifxregex.1.00 ifxrltree.2.00 mqblade.2.0 spatial.8.22.FC2 sts.2.00.FC1 wfs.1.00.FC1 A 'c' indicates DataBlade module has client files. If a module does not show up, check the prepare log. ol_informix1210_1>register ifxregex.1.00 testdb Register module ifxregex.1.00 into database testdb? [Y/n]y Registering DataBlade module... (may take a while). DataBlade ifxregex.1.00 was successfully registered in database testdb.​ ol_informix1210_1>quit Disconnecting... -- step3. checking the created regex functions $ onstat -m IBM Informix Dynamic Server Version 12.10.FC8W2WE -- On-Line -- Up 00:25:29 -- 182608 Kbytes Message Log File: /work2/INFORMIX/1210FC8W2WE/ol_informix1210_1.log09:54:09 Loading Module <$INFORMIXDIR/extend/ifxregex.1.00/ifxregex.bld> 09:54:09 The C Language Module </work2/INFORMIX/1210FC8W2WE/extend/ifxregex.1.00/ifxregex.bld> loaded $ dbschema -d testdb -f all | grep '^create.*regex'create function "informix".regex_match (lvarchar,lvarchar,integer default null,integer default null) create function "informix".regex_extract (lvarchar,lvarchar,integer default null,integer default null,integer default null) create function "informix".regex_replace (lvarchar,lvarchar,lvarchar,integer default null,integer default null,integer default null) create function "informix".regex_split (lvarchar,lvarchar,integer default null,integer default null,integer default null) create function "informix".regex_match (clob,lvarchar,integer default null,integer default null) create function "informix".regex_extract (clob,lvarchar,integer default null,integer default null,integer default null) create function "informix".regex_replace (clob,lvarchar,lvarchar,integer default null,integer default null,integer default null) create function "informix".regex_split (clob,lvarchar,integer default null,integer default null,integer default null) create function "informix".regex_release () create function "informix".regex_copts (lvarchar) create function "informix".regex_eopts (lvarchar) create function "informix".regex_htr (lvarchar) create procedure "informix".regex_set_trace (lvarchar,integer) -- step4. checking the created regex functions $ dbaccess testdb - Database selected. > select name from sysmaster:sysdbspaces where regex_match(name, '^datadbs[0-9]+[ $]'); name datadbs1 name datadbs2 name datadbs3 name datadbs11 4 row(s) retrieved. ------------------------------ SangGyu Jeong Software Engineer Infrasoft Seoul Korea, Republic of ------------------------------