I’m moving to pastures new and will be leaving behind a well-loved SQL Server database, complete with a rich array of patient level data. At my new job I will likely have to make do with an Access database and so I’ve been doing a bit of preparation, building a quick cheat sheet so I can continue to build SQL queries in the new environment.
So far I’ve been tripped up by the simplest of select queries due to the slight differences in Access SQL (JET SQL) and T-SQL (used on Microsoft SQL Server).
Eg.
T-SQL Select where Value contains…..
SELECT * FROM dbo.episodes WHERE spell_hrg like 'PB%'
in T-SQL the multi character wildcard is % while in Access it is *. Strings are also identified in access with the double speechmark “string” rather than single quotes ‘string’
Access SQL Select where Value contains…..
SELECT * FROM dbo.episodes WHERE spell_hrg like “PB*”
String Functions
Match multiple characters :
- T-SQL:
WHERE Value like ‘PB%’ - Access SQL:
WHERE Value like “PB*”[/cc]`
Match single characters:
- T-SQL:
WHERE Value like ‘PB0_Z’ - Access SQL:
WHERE Value like “PB0?Z”
Trimming blanks:
- T-SQL:
RTRIM(LTRIM(Value) - Access SQL:
TRIM(Value)
NULLS
Checking for NULLs:
- T-SQL:
WHERE Value IS NULL - Access SQL:
WHERE Value IS NULL– or –WHERE ISNULL(Value)(note the difference from T-SQL’s ISNULL)
Converting NULL values:
- T-SQL:
COALESCE(Value, ValueToReturnIfNull)– or –ISNULL(Value, ValueToReturnIfNull) - Access SQL:
NZ(Value, ValueToReturnIfNull)
Conditional Expressions
- T-SQL:
CASE WHEN Condition THEN ReturnIfTrue ELSE ReturnIfFalse END - Access SQL:
IIF(Condition, ReturnIfTrue, ReturnIfValue)
Link to a far more comprehensive Cheat Sheet