Re: Using regular expression in SQL give error SQLCODE=171 and SQLSTATE=42815

Hi Chris,

I use those REGEXP functions frequently.

I've noticed that they sometimes have issues when you pass a NULL value to
them. Try passing IFNULL( PLDESCB, '' ) to them instead of just PLDESCB.

They also seem to get called even when my code gives direction to the SQL
engine to not call them for a given row. Granted, in those cases, the
values returned by the REGEXP call get discarded because my code is
structured to not use the returned value, but I've had to make code changes
to guard against passing NULLs even when I've told the SQL engine not to
call the the REGEXP function at all for a given row.

I don't know if this is your problem or not, but it is the first thing I'd
try.

Mike

date: Mon, 13 Feb 2017 17:20:24 +0000

from: "Hiebert, Chris" <chris.hiebert@xxxxxxxxxxxxxx>
subject: RE: Using regular expression in SQL give error SQLCODE=171
and SQLSTATE=42815

I found no issues running the statement as you have entered it.

Where are you processing this cursor?

If you are executing the statement in an SQLRPGLE program you can look at
the SQLERRD array.

SQLERRD(5) Contains the position or column of a syntax error.


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.

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
Jose Perez
Sent: Tuesday, February 7, 2017 11:53 AM
To: midrange-l@xxxxxxxxxxxx
Subject: Using regular expression in SQL give error SQLCODE=171 and
SQLSTATE=42815

Anyone using regular expression in sql has faced this issue?, I'm trying
to catch a string pattern inside a string, the string pattern should be
like this (it contain 2 dashes and the pattern ###-#######-#) or it
contain the pattern I##### or IMP##### using the below sql

Declare C0 Cursor for

Select

IFNULL(REGEXP_SUBSTR(PLDESCB,'I[0-9]{5}'),'N'),

IFNULL(REGEXP_SUBSTR(PLDESCB,'IMP[0-9]{5}'),'N'),


IFNULL(REGEXP_SUBSTR(PLDESCB,'[0-9]{3}-[0-9]{7}-[0-9]{1,}'),'N')

From SESSION.T1

Where

(REGEXP_MATCH_COUNT(PLDESCB,'-')=2 And

REGEXP_like(PLDESCB,'[0-9]{3}-[0-9]{7}-[0-9]{1}[^0-9]')) or

REGEXP_LIKE(PLDESCB,'I[0-9]{5}') OR

REGEXP_LIKE(PLDESCB,'IMP[0-9]{5}')

but when opening the cursor I'm getting error SQLCODE=171 and
SQLSTATE=42815 What Am I doing wrong? if anyone could point me to the right
direction I will appreciate it.