SQL - LIKE and IN clauses

Reading PC World recently, I was spurred into action by an article about using the Like clause in SQL statements to report disparate values.

In MS Access the clause has a syntax of - Accounts.Acct LIKE "[ADS]*" which will extract records with the account field containing values beginning with A or D or S.
The * denoting the wild character.

With MS Visual FoxPro the syntax is not quite so neat, but can be achieved with the clause -
Accounts.acct LIKE "A%" OR Accounts.acct LIKE "D%" OR Accounts.acct LIKE "S%"
The wild character here is %.

An alternative approach, is to use the SQL IN clause, for which the above becomes
LEFT(accounts.acct,1) IN ("A", "D", "S")

Notice the use of the function LEFT() to test just the first character of field values.

We could even have - LIKE "%a%" - to report those records whose field contains an 'a'.
Or - LIKE "_a%" - to report those records whose field contains an 'a' in the second position.
With the '_' wild carding a single character.

When are these clauses used?
Not often is the obvious reply, due to a few reasons:-

1) SQL writers and users are not familiar with them, especially if they are not writing SQL statements every day.

2) The SQL clause syntax changes from one database to another.

3) Not often required to report disparate data values.

This last point is, I contend, a false assumption based on the restrictions imposed by the majority of todays ERP business software packages.
It need not be so!
As if available, they can often be used in normal business processing and coding structures.
To explain, it is best to look at a common example.
Suppose you have a company with numerous locations/branches spread throughout the country, say - Leeds, London, Glasgow, Cardiff, etc. and at each location the typical functional departments of Sales, Marketing, Manufacturing/Assembly, Accounts, Personnel, Warehousing all report to the local senior manager on a day to day basis. With central control of each functional department being maintained via head office.
On a Cost Centre coding basis we can have the location coded as
100 - London
200 - Glasgow
300 - Leeds
400 - Cardiff
...

and functional departments coded as
10 - Sales
15 - Marketing
21 - Manufacturing
22 - Assembly
30 - Warehousing
40 - Accounts
50 - Personnel
...

Combining the 2 elements together, assuming Location to be the primary element we get
10010 - London Sales
10015 - London Marketing
10021 - London Manufacturing
...
20010 - Glasgow Sales
20015 - Glasgow Marketing
20021 - Glasgow Manufacturing
...
30010 - Leeds Sales
30015 - Leeds Marketing
...

Such a coding structure using the standard From/To selection range can easily report on a Location basis.

But how do head office functional departments see the costs of their own departments spread across the company?
With the use of SQL LIKE and IN clauses!

Under Visual FoxPro the SQL LIKE clause for all for Accounts Depts. at all locations would be written as
CostCentre.code LIKE "___40"
The '_' wild cards a single space, so 3 are required to wild card the location element.

Being able to use such powerful SQL features can remove a lot of restrictions imposed on coding structures and similiar data elements.

So when reviewing ERP software packages for potential use/purchase, look for the flexibility to be able to use such SQL features when reviewing data and drawing reports.

You may not thing you need it today, but who knows what tomorrow brings.