SQL: The Non-standard Standard

In a previous blog titled “Supercharging Your SQL Statements,” Fred Winkler touched upon some interesting capabilities inherent to the SQL Query Language. It is an unfortunate fact that while SQL as a standard is widely accepted, the actual implementation of that standard varies considerably between vendors.

Variation from the standard may not necessarily be a bad thing, although it is quite confusing to those who aren’t expecting it. Let’s look at the TRIM example Fred used.

SELECT TRIM(FIELD) from TABLE

That should be easy enough; however, MSSQL doesn’t support the use of the TRIM function. Instead, you have to use a LTRIM (Left Trim), RTRIM (Right Trim) or a combination of both.

(MSSQL Syntax)
SELECT (LTRIM(RTRIM(FIELD))) from TABLE

Not the most pleasant expression to work with, but it gets the job done. This is a good example where deviation from the standard may make the resulting script look more confusing.

In fact concatenation, trim and substring functions are just a few that vary considerably between database implementation. Concatenation is a good example because, in the realm of MYSQL, you have to use a CONCAT function to join two strings together. For example, look at this statement where you want to join two zip code fields with a hyphen as a separator.

(MYSQL Syntax)
SELECT CONCAT(CONCAT(ZIP1,’-‘), ZIP2) from TABLE

It’s a little wordy but still fairly understandable if you can parse out the parenthesis. Now look at how MSSQL once again breaks with the standard. MSSQL uses a “+” operator for string concatenation, something many C++ programmers would be familiar with.

(MSSQL Syntax)
SELECT ZIP1 + ‘-‘ + ZIP2 FROM TABLE

This is also definitely non-standard but very readable in comparison to our first example. These differences can be frustrating, but doing things differently can sometimes also make our tasks a little easier.

Leave a Reply

Your email address will not be published. Required fields are marked *


*