Search the GP Tables
Search by TABLE NAME
Search by FIELD NAME
These are our SOP scripts. All of our scripts for Dynamics GP can be found here

Table Edits Forum Articles
Table Edits 0 Table Edits 0 Table Edits 0
Sign Up Now!Log In
Joel 12/12/2013 12:31:16 PM

SQL - Removing all non printable characters from a SQL field

I have an existing piece of code at a customer (a law firm) that takes a set of data and transforms it into an XML document and transfers it to another system. I'm using CDATA to encapsulate the notes field, because there's a lot of trouble in there. 

That has been working fine for several years, but this week a user entered an odd character (¬) in the data and it crashed the XML. I was getting XML parsing: line 1, character 265, illegal xml character errors.

So, I wrote a trigger that scrubs SY03900 and dis-allows any characters except normal keyboard characters, and the Carriage Return and Line Feed characters.

I tested the trigger using this string:

!"#$%&'()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrstuvwxyz{|}~€‚ƒ„…†‡ˆ‰Š‹ŒŽ‘’“”•–—˜™š›œžŸ ¡¢£¤¥¦§¨©ª«¬­®¯°±²³´µ¶·¸¹º»¼½¾¿ÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖ×ØÙÚÛÜÝÞßàáâãäåæçèéêëìíîïðñòóôõö÷øùúûüýþÿ


Version: All
Section: SQL Scripts

Table Definition Quick Links
All Tables
SOP Tables
RM Tables
GL Tables
POP Tables
HR Tables
PM Tables
UPR Tables
IV Tables
Olympic Tables