Re: SQL function equivalent to TestN?

I'm a huge fan of regular expressions, since they possess tremendous
power. I use them quite a lot.

Do keep in mind that invoking the regular expression engine is an expensive
operation (all that power comes with a cost), so be careful using it in
cases where very high performance is needed. As a guideline, if a
non-regular expression function can accomplish a task equally well, try to
use it instead, labor time permitting, since you're much more likely to get
better performance.

That said, there are many tasks that regular expressions can accomplish
fairly easily that coding an equivalent non-regular expression function
would be very difficult. So difficult in many cases, that you wouldn't be
able to justify the labor expense to duplicate the functionality yourself.

Glad to see regular expressions out there solving problems. They make my
job a lot easier.

This technique will likely perform better than using regular expressions to
test for a valid numeric:

values length( trim( translate( '123,456,789.00-', ' ', '01234567890,.-', '
' ) ) )

If the returned length is zero, it is a valid numeric (as defined by the
characters in translate argument 3). If the returned length is greater
than zero, it contains non-numeric characters. This technique converts all
bytes considered numeric to blanks, trims out the blanks, and tests the
length of the trimmed result. Customize translate argument 3 to your
specific definition of valid numeric characters.

Mike

date: Thu, 22 Dec 2016 16:30:53 +0000
from: "Hiebert, Chris" <chris.hiebert@xxxxxxxxxxxxxx>
subject: RE: SQL function equivalent to TestN?

We are on V7R1 and can use regular expressions to replicate that behavior.

Here is a very simple example:


WITH T1 (VFLD) AS (VALUES ('not a number'),('156,156'),('1 '),(' 1 ') )
/* in order to run INT your string can't have any commas */
select INT(replace( VFLD , ',' , '' ))
Where length(trim(vfld)) > 0 And
/* skip strings that have anything that is not a digit or a comma */
REGEXP_COUNT( trim(vfld) , '[^(\d|,)]' ) = 0 and
/* Include strings that have a digit or a comma. */
REGEXP_COUNT( trim(vfld) , '[(\d|,)]' ) > 0
;


Chris Hiebert
Senior Programmer/Analyst
Disclaimer: Any views or opinions presented are solely those of the author
and do not necessarily represent those of the company.