SQL Server WHERE clause wildcards

By Steve Endow

This morning I was working on a SQL query used to create to create new Dynamics GP customer IDs.

The customer IDs are created by using the first 6 characters of the customer name, followed by 3 numbers.  For example:

ACMETO001 - Acme Tools
ACMETO002 - Acme Tooling, Inc.

To query these customer IDs, I was using this query:

SELECT CUSTNMBR FROM RM00101 WHERE CUSTNMBR LIKE 'ACMETO%'

In this example, the query would return two records, so I knew that the next customer ID needed to be "ACMETO003".

This appeared to work fine.

Except this morning, the client found that they had a customer name with less than 6 characters, in which case this query did not work properly.

ACME001 - Acme

If I use the same query, it would look like:

SELECT CUSTNMBR FROM RM00101 WHERE CUSTNMBR LIKE 'ACME%'

Well, the problem is that this query will pick up the ACMETO001 and ACMETO002 IDs as well, and my new customer ID routine would improperly think that this is the next ID:

ACME003

This is not correct--it should be ACME001.

So how do we query all "ACME" customer IDs without including "ACMETO" customer IDs?

I had a general idea, but I hadn't used it in quite a while.  After searching for regular expression options and wildcards, I was reminded that the SQL LIKE clause supports basic regex-like wildcards.

This MSDN page discusses the SQL like clause and its wildcard options.  If you use brackets [ ], you can specify characters that you want to search for, or that you do not want to search for.  Additionally, you can use the underscore character to represent any character.

In my case, I came up with this:

SELECT CUSTNMBR FROM RM00101 WHERE CUSTNMBR LIKE 'ACME[0-9][0-9][0-9]%'

Since I know that the "ACME" customer ID will always have 3 digits on the end, I'm able to use the [0-9] wildcard three times to represent those three digits.  So the query will now pick up all ACME customer IDs, but will not pick up any ACMETO customer IDs.

The % at the end is probably not really necessary anymore, but I left it in the statement for now.

Now go and impress your friends and neighbors with this wonderful SQL trick!

Steve Endow is a Microsoft MVP for Dynamics GP and a Dynamics GP Certified IT Professional in Los Angeles.  He is also the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Google+ and Twitter

http://www.precipioservices.com