Using SQL2 Built-in Functions

Problem/Question/Abstract:

How do I go about using SQL2 built-in functions

Answer:

A problem that's rampant with many software tools today, even one as complete and comprehensive as Delphi, is that documentation on many important subjects is either incomplete, difficult to locate or, altogether missing.

SQL2 built-in functions fall into the final category. But while Delphi lacks the documentation of these topics, a lot of books are missing the topics as well! I must have pored over 10 SQL reference books before I found anything discussing the built-in functions in any detail, and still what I found was incomplete. But I don't blame any specific party for the lack of documentation on these subjects. And from my estimation, there's a good reason why you won't find much material on them, and it has a lot to do with how standards are established.

Establishing standards in any industry is an evolutionary process. As soon as a standard is put in place, some company comes up with ways to extend and enhance the standard. More companies join the fray, and then a new standard is established that incorporates the most commonly shared features of the various companies' products into the standard. The process then repeats itself.

Look at HTML! Soon after HTML 2.0 was introduced by the W3 Consortium, Netscape came along and added a bunch of proprietary features such as tables and backgrounds, which are now part of HTML 3.0. And while you can now find pretty good documentation on the standard tag set for HTML 3.0, for a while decent documentation was pretty scarce. Now the W3 Consortium is furiously working on Cascading Style Sheets to accomodate the various disparate document publishing techniques employed by the different browser vendors. Here we go again...

Going back to the subject of SQL2 built-in functions, I believe they have followed a path similar to HTML. SQL89 (SQL1) was devoid of built-in functions, so database vendors created proprietary functions to extend SQL89's lack of them. And believe me, there are a lot. For instance, Oracle has a bunch of very useful built-in functions for converting and manipulating various values such as the TO_CHAR() function, which takes a date type value and a format specification and outputs a string. With respect to SQL2, ANSI collected the most useful built-in functions from the various vendors and created a standard built-in function set with standard syntax. I will not discuss all of them here. However, what I will include are the functions that I have found most useful in my own applications.

Before I go into detailed discussions of the functions, Table 1 lists the functions and their operations:

Function Name
Parameters
Description
CAST
(value AS data_type)
Cast a value as another data type (i.e., convert a date to a string value)
CURRENT_DATE
n/a
Returns the current system date
LOWER
(string)
Converts string to all lower case
UPPER
(string)
Converts string to all upper case
SUBSTRING
(value FROM n FOR len)
Returns a portion of a string beginning at n-th character, for a length of len
TRIM
(BOTH char FROM string)
Trims char from both ends of a string (could be a space)
TRIM
(LEADING char FROM string)
Trims leading char from string
TRIM
(TRAILING char FROM string)
Trims trailing char from string


Table 1 -- List of common SQL2 Built-in Functions

CAST

Cast is a function I've found highly useful, especially when doing column concatenations in SQL. For instance, in one of my programs I created a report table for which I would be using Crystal Reports © as the reporting tool. But rather than create indexes in code, I decided to concatenate the fields that would make a record unique and use Crystal to sort the records by the resultant field during print. Here's some example code:

sqlEpi := TQuery.Create(Application);
with sqlEpi do
begin
  SQL.Add('SELECT DISTINCT D.*, (((((CAST(D."Cluster" AS VARCHAR(5)) || ');
  SQL.Add('CAST(D."FDate" AS VARCHAR(8))) || CPT4) || ICDX1) || ');
  SQL.Add('ICDX2) || ProvID) AS ClustID,');
  SQL.Add('(CAST(D."Cluster" AS VARCHAR(5)) || ClustProv) As ClustProvID');
  SQL.Add('FROM ":PRIVATE:EPIINIT7" D');
  try
    Open;
  except
    Free;
    Abort;
  end;
end;

As you can see, I used cast on the Cluster and FDate columns to convert them from a numeric and date respectively, to VARCHAR's. Notice that there's no conversion to a STRING type. For strings, you either use CHAR(n) or VARCHAR(n), where n is the size of the output string. I normally use VARCHAR(n) because I'm sometimes I'm not sure exactly how long my string will be, but I usually know the longest length.

CURRENT_DATE, LOWER, and UPPER

These three are all pretty self-explanatory. CURRENT_DATE will get you the current date returned as a Date value. LOWER and UPPER are simple case conversion functions.

SUBSTRING

I'm probably asked how to use SUBSTRING more than any other SQL2 function. Its utility is obvious. But it goes way beyond just returning a substring from a value. SUBSTRING can be used in various ways in SQL. It's such a useful function, I've employed it wherever I can to cut off values. Here are a few examples:

Using SUBSTRING in an UPDATE query:

EpiSQL := TQuery.Create(Application);
with EpiSQL do
begin
  SQL.Clear;
  SQL.Add('UPDATE ":PRIVATE:EPIINIT1.DB"');
  SQL.Add('SET CPT4 = SUBSTRING(CPT4 FROM 1 FOR 4)');
  try
    ExecSQL;
  except
    Free;
    Abort;
  end;
end;


Using SUBSTRING in the SELECT portion of query:

EpiSQL := TQuery.Create(Application);
with EpiSQL do
begin
  SQL.Clear;
  SQL.Add('SELECT D."Ingredient Cost", D."Dispensing Fee", SUBSTRING(NDC FROM 1 FOR 9) AS NDC');
  SQL.Add('FROM "' + extractTable + '" D');
  SQL.Add('WHERE (D."Fill Date" > ''' + fDate + ''')');
  try
    Open;
  except
    Free;
    Abort;
  end;
end;


Using SUBSTRING in the WHERE portion of a query:

EpiSQL := TQuery.Create(Application);
with EpiSQL do
begin
  SQL.Clear;
  SQL.Add('SELECT * FROM "EPIWORK.DB"');
  SQL.Add('WHERE SUBSTRING(ProvId FROM 1 FOR 4) = ''9201''');
  try
    Open;
  except
    Free;
    Abort;
  end;
end;

As you can see, SUBSTRING can be employed in a variety of different ways. But here's something that I should mention: SUBSTRING is not recognized by the InterBase server. To simulate that, you will have to use the LIKE operator in the where clause. Unfortunately, that's the only place where LIKE can be used. A way around this, though, is to make an initial extract from an InterBase table and output to a Paradox or dBase file. SUBSTRING on these types of tables will work.