Recently, a client was starting a SafePay & EFT implementation for Dynamics GP and one of the requirements of the bank was that the vendors cannot have any special characters outside of a handful of "regular" special characters. Their specifications and instructions had a list of acceptable characters and a longer list of unacceptable characters.

Example list of characters allowed

Here's the list of allowed characters, other than alphabet letters and numbers. This happened to be for RBC (Royal Bank) but I'm sure most banks have similar restrictions.

  • spaces
  • ! ? , . : ;
  • ( ) < >
  • single and double quotes
  • $ # & * + - / = @ %

SQL Script Example

Here's part of a script in SQL to check for characters that are outside of this list:

select *
from   MyTable
where  MyFieldName like '%[^a-zA-Z0-9 !"%#$&''()*+,-./:;<=>?@]%'

How to read this script's pieces

The breakdown of the script is this:

  • SELECT * FROM <table> WHERE <field> like is the "normal" part of the SQL script where it specifies:
    • A table to look at (PM00200 for instance, the vendor master table)
    • A field to check for special characters (VENDCHKNM for instance, the vendor cheque name).
  • '% <search string> %' is the placeholder for the matching string which could contain anything to be searched for.
    • A "%" in SQL is a wildcard so I could put '%ADAM%' and SQL would find anything in the specified field & table with "ADAM" in the text, no matter what text follows or precedes that text. The wildcard is the part that does that. 
    • Using 'ADAM%' with no % at the start, and a % at the end will search for fields where the text begins with ADAM but can end with anything else. The opposite would be true if I put % first, and removed the final %.
  • [^ <exclude string> ] is a container for exclusions to the search, because the ^ symbol specifically means "except these characters".
    • What is in that square bracket container will be excluded from the search.
    • So, what I have in my script above will find all records in <table> where <field> contains anything "except" the listing of acceptable characters I've noted.
  • a-zA-Z0-9 looks funny but if I were to put a comma or space in to separate the list, it's also going to exclude commas and spaces.
    • If I were searching for anything other than just alphanumerics, I could stop with that, end with the ]%' and the script will return any field values with non-alphanumeric characters. I don't completely understand why it knows a-z is not "a, dash and z" but it knows that is a range. Go figure.
  • After the 0-9, I put "space", and started listing each acceptable character from the bank's specification. Then, the end of the container is the ending ]%' to close the "container".

Caveats for SQL scripts with single quotes

Here are two things to watch for.

  1. When copying and pasting a script from a website, always check for quotation marks (single or double) and sometimes other characters as well. Often the font differences make the pasted script not be the proper actual character. I often re-type parts of scripts that contain quotes to make sure SQL uses the right ones.
  2. Watch for where single quotes are used. SQL treats single quotes as the start or end of a text string. In my sample script, the actual command starts with a single quote. By putting a single quote in the exclusion list, by itself, I would be ending the like statement and it would ignore anything after that point in the script.  To get around this, when there is a single quote that needs to include as text, I need to use 2 single quotes back to back, to essentially tell SQL to ignore it.

See this example where the first line is the "correct" syntax, where the entire "like" string is in red indicating it's a text string. The second script contains a single quote by itself before the first bracket "(" and everything else I typed after that is in grey, indicating it's no longer considered part of the string of text. Only when I type the end single quote does it think I'm starting another text string when I type?

SQL syntax
This screenshot shows visually the difference between single quotes vs. 2-single quotes. The colour of the text shows the bottom example is incorrect.

This script was an easy way to find vendors with unusual characters without trying to list all characters to avoid. In this case, it was a relatively short list of acceptable characters so it was easier to use the "except for" approach.

In testing this, it turns out my client didn't have any oddly named vendors so I needed to take parts of the exclusion list out, until I found the script returned some results. I.E., some vendors had the @ sign or dashes so to test the script, I would remove the exclusion for the dash or @, and run the script and get some vendors in the list.

Hope this helps someone!