Article #16153: SQL: Using the SUBSTRING Function

 Technical Information Database

TI1153D.txt   SQL: Using the SUBSTRING Function
Category   :Database Programming
Platform    :All
Product    :Delphi  All

Description:
The SQL function SUBSTRING can be used in Delphi applications that include
local SQL queries, but is not supported for InterBase (IB) or the Local
InterBase Server (LIBS) tables. What follows is the syntax for the
SUBSTRING function, examples of its use in local SQL queries, and an
alternative that will return the same results for IB/LIBS tables.

The syntax for the SUBSTRING function is:

  SUBSTRING( FROM  [, FOR ])
  
Where:

   is the name of the column in the table from which the sub-
  string is to be extracted.
  
   is the point in the column value from which the sub-string to
  be extracted will start.
  
   is the length of the sub-string to be extracted.
  
Using these values, the use of the SUBSTRING function below would return
the second, third, and fourth characters from a column named COMPANY:

  SUBSTRING(COMPANY FROM 2 FOR 3)
  
The SUBSTRING function can be used either in the field list for a SELECT
query or in the WHERE clause of a query to allow for comparing a value
with a specific sub-set of a column. The SUBSTRING function can only be
used with String type columns (the CHAR type in SQL parlance). Here is an
example of the SUBSTRING function used in a columns list in a SELECT
query (using the sample Paradox table CUSTOMER.DB):

  SELECT (SUBSTRING(C."COMPANY" FROM 1 FOR 3)) AS SS
  FROM "CUSTOMER.DB" C

This SQL query extracts the first three characters from the COMPANY
column, returning them as the calculated column named SS. Now, an example
of the SUBSTRING function used in the WHERE clause of an SQL query (using
the same sample table):

  SELECT C."COMPANY"
  FROM "CUSTOMER.DB" C
  WHERE SUBSTRING(C."COMPANY" FROM 2 FOR 2) = "an"
  
This query returns all rows from the table where the second and third
characters in the COMPANY column are "ar".

As the SUBSTRING function is not supported at all by IB or LIBS databases,
it is not possible to have a sub-string operation in the column list of
a query (exception: IB can do sub-strings via User-Defined Functions).
But through use of the LIKE operator and the accompanying character
substitution marker, it is possible to effect a sub-string in a WHERE
clause. For example, using the sample table EMPLOYEE (in the EMPLOYEE.GDB
database):

  SELECT LAST_NAME, FIRST_NAME
  FROM EMPLOYEE
  WHERE LAST_NAME LIKE "_an%"
  
This SQL query would return all rows in the table where the second and
third characters of the LAST_NAME column are "an", similar to the
previous example for the Paradox table. While IB and LIBS databases
would require this method for performing sub-string comparisons in the
WHERE clause of a query and cannot use the SUBSTRING function, Paradox and
dBASE tables (i.e., local SQL) can use either method.



Reference:


7/16/98 4:34:03 PM
 

Last Modified: 01-SEP-99