RegExp_Replace in mainframe

Database
Highlighted

RegExp_Replace in mainframe

Hi,

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')

Tags (1)
4 REPLIES 4
Ambassador

Re: RegExp_Replace in mainframe

I don't know about mainframes, but it looks like you're using a wrong session character set, e.g. ASCII instead of EBCDIC

Re: RegExp_Replace in mainframe

@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;

Ambassador

Re: RegExp_Replace in mainframe

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 ...
Teradata Employee

Re: RegExp_Replace in mainframe

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

  • verify that the hex values of the characters being sent are what Teradata expects (even if they display oddly in your editor)
  • change the code page you use to communicate with the mainframe so the display matches Teradata's interpretation (may not be feasible)
  • install & activate a client character set on Teradata which matches the code page you are using, then select that character set for your session or set as default for the HostID

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.