Author: Lou Adler
Is there a SQL equivalent to Delphi's Pos() function?
You bet. It's called the SUBSTRING function. Like the Pos function, the SUBSTRING
function in SQL will return a substring of a string based upon a range of
characters you specify. It's a handy function to have because not only can it be
used within the WHERE portion of a SQL statement to search a column based on a
substring, it can also be used in the SELECT portion of the SQL statement to return
a substring of a column.
Here's syntax for the SUBSTRING function:
SUBSTRING( FROM Starting Position FOR
Here are definitions of the various values:
FieldName This is the name of the column in your table that you will apply the
SUBSTRING function to
Starting Position This is the starting position of the Column's field value. For
instance, if you want to start at the second character, the value here would be '2.'
SubString Length This is the length of the Substring itself. It can be any value
greater than 0.
To see how SUBSTRING can be employed in the SELECT and WHERE clauses, let's look at
a couple of examples. First, let's see how we can use the SUBSTRING function to
search a column based on a substring of that column.
Let's say I want to search a customer database for all names beginning with 'DEL'
in the LastName field of my database. Here's some simple SQL that will accomplish
SELECT * FROM "CUSTOMER"
WHERE SUBSTRING(LastName FROM 1 FOR 3) = 'DEL'
This SQL statement will return all rows that start with 'DEL.'
The SUBSTRING Function's Secret Power
Now here's where I think the SUBSTRING function really shines. I have found that in
many cases, I'm not interested in extracting the entire value of a particular
field. For example, I work in health care analysis (specifically drug benefits). In
our claims database, drugs are assigned specific identification numbers in string
format, called an NDC. The identifiers are interesting in that they are
hierarchical in nature. For example, the identifier is an 11-digit string. The
first two characters of the string represent the drug manufacturer; the the first
nine digits represent the manufacturer, brand, and drug classification. The full
string gives all the information from the previous examples, plus the strength and
When I'm called upon to perform drug analysis, my users typically aren't interested
in the strength and dosage of the drugs, so they request that I only include the
nine-digit drug classification level in my analysis. For instance, they may request
the costs associated with all drug classifications. This is easily accomplished
with the following SQL statement:
SELECT D."Drug Cost", D."Amount Due", SUBSTRING(NDC FROM 1 FOR 9) AS NDC9DIGIT
FROM ":Customer:CLAIMS.DB" D
WHERE (D."Fill Date" >= '1/1/96')
Note: We're assuming the destination table to be :PRIV:Answer.db
Since the query above will create duplicate values in the NDC column and we want
distinct NDCs reported, we do one more query to summarize the cost and amount due
columns and aggregate them on the distinct NDCs.
SELECT DISTINCT NDC9DIGIT,
SUM(D."Drug Cost") AS D."Drug Cost",
SUM(D."Amount Due") AS D."Amount Due"
ORDER BY NDC9DIGIT
This query's answer table will now have the cost and amount due values rolled up to
the distinct NDCs.
SUBSTRING can add a lot to your application by providing a means to look at your
data in a lot of different ways. Especially where the column values you are
applying SUBSTRING to are hierarchical or categorical in nature, SUBSTRING will
prove to be an indispensable function.
One thing to note: Many server databases don't support the SUBSTRING function. In most cases, you have to use the LIKE operator to simulate SUBSTRING's functionality. In other cases, they have their own proprietary functions to handle substrings. You should check your server databases's documentation to see what the equivalent would be.