LIKE and Regular Expressions AKA regex in SQL
Jacob Salomon needed SQL matching names like sbstemp1 or sbstemp13 (one or more trailing digits) without LIKE matching too much. Paul Watson suggested the regexp datablade; Art Kagel said MATCHES could do it; Theo B gave REGEX_MATCH with '^sbstemp[0-9]+$'. Eric Vercelletto confirmed it and noted regex arrived in 12.10 xC8; Hrvoje Zokovic shared a presentation on it.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
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
Use the regexp datablade – it's built in now, for older engines you can get a fixed version from http://www.oninit.com/download/index.php?page=regexp.html – the version on the IBM website doesn't work in all installations Cheers Paul ------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
------------------------------
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 ------------------------------
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] ------------------------------
Eric, looking for this: https://zokovic.eu/B02-REGEX.pdf Regards Hrvoje ------------------------------ Hrvoje Zokovic ------------------------------