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
Substring Length)
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 that:
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 dosage administered.
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"
FROM ":PRIV:Answer.DB"
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.
Copyright © 1997 Brendan V. Delumpa All Rights Reserved
|