Question:
Is there a SQL equivalent to Delphi’s Pos() function?
Answer:
You bet. It’s called the SUBSTRING function. Like the Pos function,the SUBSTRINGfunction in SQL will return a substring of a string based upon a range ofcharacters youspecify. It’s a handy function to have because not only can it be usedwithin the WHERE portionof a SQL statement to search a column based on a substring, it can also beused in theSELECT portion of the SQL statement to return a substring of a column.
Here’s syntax for the SUBSTRING function:
SUBSTRING(FROM Starting Position FORSubstring 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’slook at a coupleof examples. First, let’s see how we can use the SUBSTRING functionto search a columnbased 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 SQLthat willaccomplish 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 foundthat in many cases,I’m not interested in extracting the entire value of a particular field.For example, I work inhealth care analysis (specifically drug benefits). In our claims database,drugs are assignedspecific identification numbers in string format, called an NDC. Theidentifiers are interesting in that theyare hierarchical in nature. For example, the identifier is an 11-digitstring. The first twocharacters of the string represent the drug manufacturer; the the first ninedigits represent themanufacturer, brand, and drug classification. The full string gives all theinformation fromthe previous examples, plus the strength and dosage administered.
When I’m called upon to perform drug analysis, my users typicallyaren’t interested in thestrength and dosage of the drugs, so they request that I only include thenine-digit drug classificationlevel in my analysis. For instance, they may request the costsassociated with all drugclassifications. This is easily accomplished with the following SQL statement:
SELECT D.”Drug Cost”, D.”Amount Due”, SUBSTRING(NDC FROM 1 FOR 9) AS NDC9DIGITFROM “:Customer:CLAIMS.DB” DWHERE (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 wewant distinct NDCsreported, we do one more query to summarize the cost and amount due columnsand aggregate themon 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 valuesrolled up to the distinctNDCs.
SUBSTRING can add a lot to your application by providing a means to look atyour data in a lotof different ways. Especially where the column values you are applyingSUBSTRING to are hierarchicalor categorical in nature, SUBSTRING will prove to be an indispensable function.
One thing to note: InterBase doesn’t support the SUBSTRINGfunction. Inthat case, you have to use the LIKE operator to simulate SUBSTRING’sfunctionality.
