Regular Expression Brackets not working in BTEQ

Database

Regular Expression Brackets not working in BTEQ

Hi I am trying to run some sql statements with regular expressions through a BTEQ and there seems to be some kind of an issue.

The SQL statement runs fine in TERADATA SQL assistant, but

whe I run this sql statement through a BTEQ on mainframe


SELECT CAST(COUNT(*) AS BIGINT) FROM DB_NM.TB_NM WHERE not ((REGEXP_SIMILAR(TRIM(COL_NM), '[0-9]+', 'c')=1 or REGEXP_SIMILAR(TRIM(COL_NM),  '[+-]?[0-9]+.', 'c')=1)) and not TRIM(COL_NM) = '';

and it actually runs

SELECT CAST(COUNT(*) AS BIGINT) FROM DB_NM.TB_NM WHERE not ((REGEXP_SIMILAR(TRIM(COL_NM), '.0-9.+', 'c')=1 or REGEXP_SIMILAR(TRIM(COL_NM),  '.+-.?.0-9.+.', 'c')=1)) and not TRIM(COL_NM) = '';

If anyone has any suggestions on how fix this it would be great.

1 REPLY
Teradata Employee

Re: Regular Expression Brackets not working in BTEQ

Thanks for starting a new thread.

Look at the EBCDIC characters in Hex. What values do you see for the special characters? There are multiple variations of EBCDIC and square brackets in particular can be an issue even within IBM applications.

For Teradata's default old-style EBCDIC client character set mapping, the values need to be:

[ x'AD' Left Square Bracket

] x'BD' Right Square Bracket

If that's not what you see on your screen, perhaps you can change terminal options.

Or you can edit the SQL statement in hex to provide the characters Teradata will interpret as square brackets.

Or if you enable the supplied optional EBCDIC037_0E (Code Page 037) client character set on the database, and use that for your session, the values would need to be:

[ x'BA' Left Square Bracket

] x'BB' Right Square Bracket