Hi Folks.

I have need for SQL that will match strings like sbstemp1 or sbstemp13.

That it; my whole question in one sentence.  Lest you suspect that I'm an impostor posing as Jacob, here is some commentary n wht I have tried:

The Guide to SQL Syntax tells me all about a single-digit match, so I could use: LIKE "^sbstemp[0-9]%$". Main problem with this:

• This will match "sbstemp1xyz", which is not what I want to match; it MUST end in 1 or more digits.

In Perl code I would use =~ /^sbstemp\d+$/ That is: The string "sbstemp" followed by one or more digits.  AFAIK that won't fly in SQL.

Currently, I'm matching LIKE "sbstemp%" but this is also not satisfactory because it matches "sbstemp" - without trailing digits, which is NOT a string I want it to match.  It works now only because nobody here has named a database sbstemp.  But anyone who knows me knows I really don't trust probabilistic solutions.  (Nah, it'll never happen so you don't have to check for it.)  I did once read through an entire book on Murphy's lay and corollaries and it confirmed my paranoid suspicions about users. :-)

Thanks for ideas...

------------------------------
Jacob Salomon
---
Nobody goes there anymore, it's too crowded.  --Attr: Yogi Berra
------------------------------

#Informix

Original Message------

Hi Folks.

I have need for SQL that will match strings like sbstemp1 or sbstemp13.

That it; my whole question in one sentence.  Lest you suspect that I'm an impostor posing as Jacob, here is some commentary n wht I have tried:

The Guide to SQL Syntax tells me all about a single-digit match, so I could use: LIKE "^sbstemp[0-9]%$". Main problem with this:

• This will match "sbstemp1xyz", which is not what I want to match; it MUST end in 1 or more digits.

In Perl code I would use =~ /^sbstemp\d+$/ That is: The string "sbstemp" followed by one or more digits.  AFAIK that won't fly in SQL.

Currently, I'm matching LIKE "sbstemp%" but this is also not satisfactory because it matches "sbstemp" - without trailing digits, which is NOT a string I want it to match.  It works now only because nobody here has named a database sbstemp.  But anyone who knows me knows I really don't trust probabilistic solutions.  (Nah, it'll never happen so you don't have to check for it.)  I did once read through an entire book on Murphy's lay and corollaries and it confirmed my paranoid suspicions about users. :-)

Thanks for ideas...

------------------------------
Jacob Salomon
---
Nobody goes there anymore, it's too crowded.  --Attr: Yogi Berra
------------------------------

#Informix

Jacob:

There is a REGEX datablade you could use for more complex stuff, but this one you can do with a simple MATCHES:


> select tabname from mytables where tabname matches 'frag*[0-9]';

tabname  fragtest2

tabname  fragtest3

2 row(s) retrieved.

> select count(*) from mytables;

     (count(*))  

            313

1 row(s) retrieved.

------------------------------
Art Kagel
------------------------------

Original Message:
Sent: Wed February 26, 2020 09:49 AM
From: Jacob Salomon
Subject: LIKE and Regular Expressions AKA regex in SQL

Hi Folks.

I have need for SQL that will match strings like sbstemp1 or sbstemp13.

That it; my whole question in one sentence.  Lest you suspect that I'm an impostor posing as Jacob, here is some commentary n wht I have tried:

The Guide to SQL Syntax tells me all about a single-digit match, so I could use: LIKE "^sbstemp[0-9]%$". Main problem with this:

• This will match "sbstemp1xyz", which is not what I want to match; it MUST end in 1 or more digits.

In Perl code I would use =~ /^sbstemp\d+$/ That is: The string "sbstemp" followed by one or more digits.  AFAIK that won't fly in SQL.

Currently, I'm matching LIKE "sbstemp%" but this is also not satisfactory because it matches "sbstemp" - without trailing digits, which is NOT a string I want it to match.  It works now only because nobody here has named a database sbstemp.  But anyone who knows me knows I really don't trust probabilistic solutions.  (Nah, it'll never happen so you don't have to check for it.)  I did once read through an entire book on Murphy's lay and corollaries and it confirmed my paranoid suspicions about users. :-)

Thanks for ideas...

------------------------------
Jacob Salomon
---
Nobody goes there anymore, it's too crowded.  --Attr: Yogi Berra
------------------------------

#Informix

Hi Jacob,

This could fit your needs:

... WHERE REGEX_MATCH(FIELD_NAME, '^sbstemp[0-9]+$')​​

or

... WHERE REGEX_MATCH(FIELD_NAME, '^sbstemp[[:digit:]]+$')​

Regards,
Theo

------------------------------
Theo B
------------------------------

Original Message:
Sent: Wed February 26, 2020 09:49 AM
From: Jacob Salomon
Subject: LIKE and Regular Expressions AKA regex in SQL

Hi Folks.

I have need for SQL that will match strings like sbstemp1 or sbstemp13.

That it; my whole question in one sentence.  Lest you suspect that I'm an impostor posing as Jacob, here is some commentary n wht I have tried:

The Guide to SQL Syntax tells me all about a single-digit match, so I could use: LIKE "^sbstemp[0-9]%$". Main problem with this:

• This will match "sbstemp1xyz", which is not what I want to match; it MUST end in 1 or more digits.

In Perl code I would use =~ /^sbstemp\d+$/ That is: The string "sbstemp" followed by one or more digits.  AFAIK that won't fly in SQL.

Currently, I'm matching LIKE "sbstemp%" but this is also not satisfactory because it matches "sbstemp" - without trailing digits, which is NOT a string I want it to match.  It works now only because nobody here has named a database sbstemp.  But anyone who knows me knows I really don't trust probabilistic solutions.  (Nah, it'll never happen so you don't have to check for it.)  I did once read through an entire book on Murphy's lay and corollaries and it confirmed my paranoid suspicions about users. :-)

Thanks for ideas...

------------------------------
Jacob Salomon
---
Nobody goes there anymore, it's too crowded.  --Attr: Yogi Berra
------------------------------

#Informix

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
------------------------------

Original Message:
Sent: Wed February 26, 2020 10:36 AM
From: Theo B
Subject: LIKE and Regular Expressions AKA regex in SQL

Hi Jacob,

This could fit your needs:

... WHERE REGEX_MATCH(FIELD_NAME, '^sbstemp[0-9]+$')​​

or

... WHERE REGEX_MATCH(FIELD_NAME, '^sbstemp[[:digit:]]+$')​

Regards,
Theo

------------------------------
Theo B

Original Message:
Sent: Wed February 26, 2020 09:49 AM
From: Jacob Salomon
Subject: LIKE and Regular Expressions AKA regex in SQL

Hi Folks.

I have need for SQL that will match strings like sbstemp1 or sbstemp13.

That it; my whole question in one sentence.  Lest you suspect that I'm an impostor posing as Jacob, here is some commentary n wht I have tried:

The Guide to SQL Syntax tells me all about a single-digit match, so I could use: LIKE "^sbstemp[0-9]%$". Main problem with this:

• This will match "sbstemp1xyz", which is not what I want to match; it MUST end in 1 or more digits.

In Perl code I would use =~ /^sbstemp\d+$/ That is: The string "sbstemp" followed by one or more digits.  AFAIK that won't fly in SQL.

Currently, I'm matching LIKE "sbstemp%" but this is also not satisfactory because it matches "sbstemp" - without trailing digits, which is NOT a string I want it to match.  It works now only because nobody here has named a database sbstemp.  But anyone who knows me knows I really don't trust probabilistic solutions.  (Nah, it'll never happen so you don't have to check for it.)  I did once read through an entire book on Murphy's lay and corollaries and it confirmed my paranoid suspicions about users. :-)

Thanks for ideas...

------------------------------
Jacob Salomon
---
Nobody goes there anymore, it's too crowded.  --Attr: Yogi Berra
------------------------------

#Informix

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

------------------------------
Andreas Legner
------------------------------

Original Message:
Sent: Wed February 26, 2020 11:24 AM
From: Jacob Salomon
Subject: 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

Original Message:
Sent: Wed February 26, 2020 10:36 AM
From: Theo B
Subject: LIKE and Regular Expressions AKA regex in SQL

Hi Jacob,

This could fit your needs:

... WHERE REGEX_MATCH(FIELD_NAME, '^sbstemp[0-9]+$')​​

or

... WHERE REGEX_MATCH(FIELD_NAME, '^sbstemp[[:digit:]]+$')​

Regards,
Theo

------------------------------
Theo B

Original Message:
Sent: Wed February 26, 2020 09:49 AM
From: Jacob Salomon
Subject: LIKE and Regular Expressions AKA regex in SQL

Hi Folks.

I have need for SQL that will match strings like sbstemp1 or sbstemp13.

That it; my whole question in one sentence.  Lest you suspect that I'm an impostor posing as Jacob, here is some commentary n wht I have tried:

The Guide to SQL Syntax tells me all about a single-digit match, so I could use: LIKE "^sbstemp[0-9]%$". Main problem with this:

• This will match "sbstemp1xyz", which is not what I want to match; it MUST end in 1 or more digits.

In Perl code I would use =~ /^sbstemp\d+$/ That is: The string "sbstemp" followed by one or more digits.  AFAIK that won't fly in SQL.

Currently, I'm matching LIKE "sbstemp%" but this is also not satisfactory because it matches "sbstemp" - without trailing digits, which is NOT a string I want it to match.  It works now only because nobody here has named a database sbstemp.  But anyone who knows me knows I really don't trust probabilistic solutions.  (Nah, it'll never happen so you don't have to check for it.)  I did once read through an entire book on Murphy's lay and corollaries and it confirmed my paranoid suspicions about users. :-)

Thanks for ideas...

------------------------------
Jacob Salomon
---
Nobody goes there anymore, it's too crowded.  --Attr: Yogi Berra
------------------------------

#Informix

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
------------------------------

Original Message:
Sent: Wed February 26, 2020 11:24 AM
From: Jacob Salomon
Subject: 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

Original Message:
Sent: Wed February 26, 2020 10:36 AM
From: Theo B
Subject: LIKE and Regular Expressions AKA regex in SQL

Hi Jacob,

This could fit your needs:

... WHERE REGEX_MATCH(FIELD_NAME, '^sbstemp[0-9]+$')​​

or

... WHERE REGEX_MATCH(FIELD_NAME, '^sbstemp[[:digit:]]+$')​

Regards,
Theo

------------------------------
Theo B

Original Message:
Sent: Wed February 26, 2020 09:49 AM
From: Jacob Salomon
Subject: LIKE and Regular Expressions AKA regex in SQL

Hi Folks.

I have need for SQL that will match strings like sbstemp1 or sbstemp13.

That it; my whole question in one sentence.  Lest you suspect that I'm an impostor posing as Jacob, here is some commentary n wht I have tried:

The Guide to SQL Syntax tells me all about a single-digit match, so I could use: LIKE "^sbstemp[0-9]%$". Main problem with this:

• This will match "sbstemp1xyz", which is not what I want to match; it MUST end in 1 or more digits.

In Perl code I would use =~ /^sbstemp\d+$/ That is: The string "sbstemp" followed by one or more digits.  AFAIK that won't fly in SQL.

Currently, I'm matching LIKE "sbstemp%" but this is also not satisfactory because it matches "sbstemp" - without trailing digits, which is NOT a string I want it to match.  It works now only because nobody here has named a database sbstemp.  But anyone who knows me knows I really don't trust probabilistic solutions.  (Nah, it'll never happen so you don't have to check for it.)  I did once read through an entire book on Murphy's lay and corollaries and it confirmed my paranoid suspicions about users. :-)

Thanks for ideas...

------------------------------
Jacob Salomon
---
Nobody goes there anymore, it's too crowded.  --Attr: Yogi Berra
------------------------------

#Informix

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
------------------------------

Original Message:
Sent: Wed February 26, 2020 11:44 AM
From: Art Kagel
Subject: LIKE and Regular Expressions AKA regex in SQL

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

Original Message------

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
------------------------------

#Informix

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

$ blademgr
ol_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.log
09: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
------------------------------

Original Message:
Sent: Wed February 26, 2020 12:29 PM
From: Jacob Salomon
Subject: LIKE and Regular Expressions AKA regex in SQL

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

Original Message:
Sent: Wed February 26, 2020 11:44 AM
From: Art Kagel
Subject: LIKE and Regular Expressions AKA regex in SQL

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

Hi Jacob,


Theo's solution is the solution you are looking for. Regex has been implemented in 12.10 xC8 if I remember well. A great functionality that almost noone uses, although it can resolve so many situations ...

Yes REGEX are a  great functionality! I could not leave without REGEX today!

The problem is that the datablade is very poorly documented in IBM doc. Nevertheless Mark A. (AKA 'the farmer' ) did a good presentation at IIUG Conf in Raleigh/NC in 2017, with significant examples and explainations

I have the presentation that I can send to you, seems that our iiug site has a problem on that URL :-)

Unless I can upload the file here

Cheers

Eric


------------------------------
[eric] [Verceletto] []
[Founder]
[kandooerp.org]
[Pont l'Abbé] [France]
[+33 626 52 50 68]
------------------------------

Original Message:
Sent: Wed February 26, 2020 09:49 AM
From: Jacob Salomon
Subject: LIKE and Regular Expressions AKA regex in SQL

Hi Folks.

I have need for SQL that will match strings like sbstemp1 or sbstemp13.

That it; my whole question in one sentence.  Lest you suspect that I'm an impostor posing as Jacob, here is some commentary n wht I have tried:

The Guide to SQL Syntax tells me all about a single-digit match, so I could use: LIKE "^sbstemp[0-9]%$". Main problem with this:

• This will match "sbstemp1xyz", which is not what I want to match; it MUST end in 1 or more digits.

In Perl code I would use =~ /^sbstemp\d+$/ That is: The string "sbstemp" followed by one or more digits.  AFAIK that won't fly in SQL.

Currently, I'm matching LIKE "sbstemp%" but this is also not satisfactory because it matches "sbstemp" - without trailing digits, which is NOT a string I want it to match.  It works now only because nobody here has named a database sbstemp.  But anyone who knows me knows I really don't trust probabilistic solutions.  (Nah, it'll never happen so you don't have to check for it.)  I did once read through an entire book on Murphy's lay and corollaries and it confirmed my paranoid suspicions about users. :-)

Thanks for ideas...

------------------------------
Jacob Salomon
---
Nobody goes there anymore, it's too crowded.  --Attr: Yogi Berra
------------------------------
#Informix

Eric, looking for this:
https://zokovic.eu/B02-REGEX.pdf
Regards
Hrvoje

------------------------------
Hrvoje Zokovic
------------------------------

Original Message:
Sent: Wed February 26, 2020 12:53 PM
From: Eric Vercelletto
Subject: LIKE and Regular Expressions AKA regex in SQL

Hi Jacob,


Theo's solution is the solution you are looking for. Regex has been implemented in 12.10 xC8 if I remember well. A great functionality that almost noone uses, although it can resolve so many situations ...

Yes REGEX are a  great functionality! I could not leave without REGEX today!

The problem is that the datablade is very poorly documented in IBM doc. Nevertheless Mark A. (AKA 'the farmer' ) did a good presentation at IIUG Conf in Raleigh/NC in 2017, with significant examples and explainations

I have the presentation that I can send to you, seems that our iiug site has a problem on that URL :-)

Unless I can upload the file here

Cheers

Eric


------------------------------
[eric] [Verceletto] []
[Founder]
[kandooerp.org]
[Pont l'Abbé] [France]
[+33 626 52 50 68]

Original Message:
Sent: Wed February 26, 2020 09:49 AM
From: Jacob Salomon
Subject: LIKE and Regular Expressions AKA regex in SQL

Hi Folks.

I have need for SQL that will match strings like sbstemp1 or sbstemp13.

That it; my whole question in one sentence.  Lest you suspect that I'm an impostor posing as Jacob, here is some commentary n wht I have tried:

The Guide to SQL Syntax tells me all about a single-digit match, so I could use: LIKE "^sbstemp[0-9]%$". Main problem with this:

• This will match "sbstemp1xyz", which is not what I want to match; it MUST end in 1 or more digits.

In Perl code I would use =~ /^sbstemp\d+$/ That is: The string "sbstemp" followed by one or more digits.  AFAIK that won't fly in SQL.

Currently, I'm matching LIKE "sbstemp%" but this is also not satisfactory because it matches "sbstemp" - without trailing digits, which is NOT a string I want it to match.  It works now only because nobody here has named a database sbstemp.  But anyone who knows me knows I really don't trust probabilistic solutions.  (Nah, it'll never happen so you don't have to check for it.)  I did once read through an entire book on Murphy's lay and corollaries and it confirmed my paranoid suspicions about users. :-)

Thanks for ideas...

------------------------------
Jacob Salomon
---
Nobody goes there anymore, it's too crowded.  --Attr: Yogi Berra
------------------------------
#Informix