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.
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.
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.
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.