Replacing apostrophes and ampersan

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
Enthusiast

Replacing apostrophes and ampersan

I'm using Report Builder 3.0 for a report that queries some Teradata databases.  A user has notified us that they can't export the results to more recent versions of Excel.  They get a message about illegal xml characters.  I've searched through the results and it appears that the illegal characters are apostrophes (') and ampersans (&).  I done some research and came across the OREPLACE function to replace these characters in 4 columns.  Below are the 4 select statements where I used this feature.

 

When I try and run the SQL now, I get a "SELECT Failed [9804] Response Row size or Constant row size  overflow" error message.

 

I'm assuming that my syntac is wrong or I've fouled something else up.  Is there a better way to replace these 2 special characters?

 

thanks for the help.....

 

 

CASE WHEN j.acct_name IS NOT NULL THEN oreplace(j.acct_name, '''','') ELSE dv.dvr_srnm || ', '   ||     dv.dvr_frst_name || ' (DRIVER)' end AS "ACCOUNT/DRIVER NAME",

 

oreplace(oreplace(journal_desc, '&', 'and'),'''',' ') AS "Jrnl Description",

 

oreplace(e.FIRST_NAME,'''', '') ||' '|| oreplace(e.LAST_NAME,'''','') AS AdjBy,

 

oreplace(oreplace(reason_desc, '&', 'and'),'''',' ') AS "reason desc",

 


Accepted Solutions
Teradata Employee

Re: Replacing apostrophes and ampersan

The result of OREPLACE is always VARCHAR(8000), so you will need to CAST the result back to a reasonable max length.

1 ACCEPTED SOLUTION
2 REPLIES
Teradata Employee

Re: Replacing apostrophes and ampersan

The result of OREPLACE is always VARCHAR(8000), so you will need to CAST the result back to a reasonable max length.

Enthusiast

Re: Replacing apostrophes and ampersan

Thanks for the information.  I appreciate your help