Author: Lou Adler 

Is there a SQL equivalent to Delphi's Pos() function?

Answer:

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.