How do I go about using SQL2
built-in functions
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.
Copyright © 1997 Brendan V. Delumpa All Rights Reserved
|