Support Article
Regexp_replace function fails in Connect-SQL
SA-21071
Summary
A Connect-SQL record browse operation fails with a lexical error after upgrade from Oracle 11R2, to Oracle 12C. The Oracle upgrade required a change from the Translate function to 'regexp_replace' function calls. Now a SQL query with the updated syntax fails to execute.
Error Messages
Caused by: com.pega.pegarules.data.internal.access.rdb.TokenMgrError: Lexical error at line 1, column 183. Encountered: "1" (49), after : "\\"
Steps to Reproduce
Example SQL:
SELECT YOURNUMBER,
regexp_replace(listagg( to_char(REQUIREDRENEWALDATE,'mm/dd/yyyy'),',') within group (order by to_char(REQUIREDRENEWALDATE,'mm/dd/yyyy')),'([^,]+)(,\1)+', '\1') as REQUIREDRENEWALDATE
. . .
Root Cause
There was a syntax error in the SQL statement: Backslash character in the function parameters is not parsed properly as it is not preceded by an escape character.
Resolution
Perform the following local-change: Change occurrences of '\1' in the query to '\\1'. The backslash is a necessary escape character.
Published March 18, 2016 - Updated October 8, 2020
Have a question? Get answers now.
Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.