I am using the following reg exp to replace special characters with a space in mainframe
RegExp_Replace(COLUMN_NAME, '[^a-z0-9]+', ' ', 1, 0, 'i')
The above functions works fine in teradata sql assistant whereas it not working as expected in mainframe and it returns like below
RegExp_Replace(COLUMN_NAME, ' a-z0-9 +', ' ', 1, 0, 'i')
@dnoeth Thanks for your response,
I am using the below before my DML, is this correct or should i need to change it to different
SET SESSION COLLATION EBCDIC;
No, this sets the Session Collation, i.e. sort order.
The Session Character Set ist set at logon, e.g. in BTEQ there's
.SET SESSION CHARSET 'EBCDIC'; .LOGON ...
There are many different "EBCDIC" code pages, and the encodings for special characters may vary depending on the code page.
The default Teradata EBCDIC client character set expects 0xAD and 0xBD for the left and right square bracket characters, respectively.
Many other EBCDIC code pages use 0xBA and 0xBB for these characters.
When you need to use special characters on the mainframe, you can either
For example, Teradata supplies several variants such as EBCDIC037_0E. You could also create a site-defined client character set. See the International Character Set Support manual for more details.