Using the LocalSQL SubString() Function

Problem/Question/Abstract:

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.