SQL Substring – use of the Substring function

The SQL SUBSTRING function cuts out and returns the specified number of characters from the string.

SQL-function SUBSTRING can be used in Delphi applications working with queries to local SQL, but it is not supported when working with InterBase (IB) and Local InterBase Server (LIBS) tables. Below is the syntax of the SUBSTRING function, examples of its use in local SQL queries, and an alternative to return the same results for IB/LIBS tables.

The first parameter the function accepts a field or a string, the second parameter – from which position to start cutting (the numbering of characters starts with 1), the third parameter – how many characters to take.

The third parameter is optional. If it is not specified, the text will be cut out from the specified position to the end of the line.

Syntax of the Substring function

First syntax:

SELECT SUBSTRING (field where to cut off, how many_symbols to take) FROM table name WHERE condition

Alternative syntax:

SELECT SUBSTRING(FROM field from where_Cut FOR how many_symbols_to take) FROM table name WHERE condition

SUBSTRING(<column> FROM <start> [, FOR <length>])

Where:

• <column> – column name of the table, from which substring should be obtained.
• <start> – place in the value of the column from which the substring is extracted.
• <length> – length of substring to be extracted.

The third parameter is optional, in which case the text will be cut out from the specified position until the end of the line:

SELECT SUBSTRING FROM table name WHERE condition
SELECT SUBSTRING(FROM field from where_cut) FROM name_table WHERE condition

The SUBSTRING function in the example below will return the second, third and fourth characters from the column named COMPANY:

(COMPANY FROM 2 TO 3)

The SUBSTRING function can also be used for a list of fields in the SELECT query, where the WHERE keyword allows comparison of the value with a specific set of columns. The SUBSTRING function can only be used with String type columns (SQL type CHAR). Here is an example of SUBSTRING function using column list in SELECT query (use Paradox CUSTOMER.DB demo table):

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

This SQL query extracts the first three characters from the COMPANY column returned as a computed column with the name SS. Here is an example of the SUBSTRING function used in an SQL query with the WHERE keyword (we use the same table):

SELECT C. "COMPANY"
FROM "CUSTOMER.DB" C
WHERE SUBSTRING(C. "COMPANY" FROM 2 FOR 2) = "an"

This query will return all table rows where the second and third characters in the COMPANY column are equal to “ar”.

Since SUBSTRING function is not supported in IB and LIBS databases, substring operations with the list of columns in the query are impossible (exception: IB can work with substrings through user-defined functions, User-Defined Functions). But with the help of the LIKE operator and the associated symbolic wildcard markers it is also possible to work with the wildcard in the case of WHERE. Here is an example based on the EMPLOYEE table (in the EMPLOYEE.GDB database):

SELECT LAST_NAME, FIRST_NAME
FROM EMPLOYEE
WHERE LAST_NAME LIKE "_an%"

This SQL query will return all table rows where the second and third characters in the LAST_NAME column are equal to “an”, see the previous example based on the Paradox table. Databases IB and LIBS to perform a substring comparison in the query operator WHERE this method is necessary (and can not use the function SUBSTRING), while Paradox and dBASE tables (eg, local SQL) can use any method.

Examples

All examples will be in this table, unless otherwise stated:

Example №1
In this example, 6 characters are cut out of the string and returned starting from the 5th character:

SELECT id, SUBSTRING(text, 5, 6) as text FROM texts

The SQL query will select the following lines:

You can rewrite the request in the following form:

SELECT id, SUBSTRING(text FROM 5 FOR 6) as text FROM texts

Example №2
In this example, the entire line is returned to the end, starting with the fifth character:

SELECT id, SUBSTRING(text, 5) as text FROM texts

The SQL query will select the following lines:

You can rewrite the request in the following form:

SELECT id, SUBSTRING(text FROM 5) as text FROM texts

SQL SUBSTRING functions allows you to extract a substring from a string