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 characters that were allowed, 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 MyTable where MyFieldName like –> this is the “normal” part of the SQL script where you are specifying a table to look at (PM00200 for instance, the vendor master table) and a field to check for special characters (VENDCHKNM for instance - vendor cheque name).
• ’% stuffgoeshere %’ --> this is the placeholder for the matching string where you could put anything you want.
  • % 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 preceeds that text.
  • The wildcard is the part that does that. ‘ADAM%’ for example, with a trailing %, looks for fields where the text begins with ADAM but can end with anything else.
• [^ stuffgoeshere ] --> this is a container for exclusions to the search, because the ^ symbol specifically means “except these characters”.
  • You might be thinking you need to list off every alphabetical character, or separate everything with commas but literally, what you type in that square bracket container will be excluded from the search.
  • So, if you were to dissect what I have in my script above, I am telling SQL, find all records in MyTable where MyFieldName contains anything “except” and I’ve listed off every acceptable character.
• a-zA-Z0-9 –> granted, this 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.
  • In my case, that is fine, but sometimes you need to be literal.
  • If you were searching for anything other than just alphanumerics, you 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 have put “space”, and started listing each individual 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. One - if you copy and paste a script from a website, always check your 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.

The second thing to watch for is where you use single quotes, SQL assumes you are ending a text string (or starting one). In my sample script, the like command starts off with a single quote. By putting a single quote in the exclusion list, by itself, I would actally be ending the like statement and it would ignore anything after that point in the script.  To get around this, when you have a single quote that you need to include as text, you actually 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.

This script was an easy way to find vendors with unusual characters without trying to list all characters you can’t use. 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 actually 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!