Using SQL2 Built-in Functions

Question:
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 NameParametersDescription
CAST(value AS data_type)Cast a value as another data type (i.e., convert a date to a string value)
CURRENT_DATEn/aReturns 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.

Share the Post:
Share on facebook
Share on twitter
Share on linkedin

Overview

The Latest

homes in the real estate industry

Exploring the Latest Tech Trends Impacting the Real Estate Industry

The real estate industry is changing thanks to the newest technological advancements. These new developments — from blockchain and AI to virtual reality and 3D printing — are poised to change how we buy and sell homes. Real estate brokers, buyers, sellers, wholesale real estate professionals, fix and flippers, and beyond may

man on floor with data

DevX Quick Guide to Data Ingestion

One of the biggest trends of the 21st century is the massive surge in internet usage. With major innovations such as smart technology, social media, and online shopping sites, the internet has become an essential part of everyday life for a large portion of the population. Due to this internet

payment via phone

7 Ways Technology Has Changed Traditional Payments

In today’s digital world, technology has changed how we make payments. From contactless cards to mobile wallets, it’s now easier to pay for goods and services without carrying cash or using a checkbook. This article will look at seven of the most significant ways technology has transformed traditional payment methods.