9.4.2 SUBSTR

Features: Extracts part of a character string.

Specification format

General rules

SUBSTR returns the number of characters specified in the third argument (starting from the position specified in the second argument) from the string specified in the first argument.
When the starting position is positive, the starting position will be from the beginning of the character value expression.
When the starting position is 0, it will be that same as if 1 is specified in the starting position.
When the starting position is negative, the starting position will be from the end of the character value expression.
When string length is not specified, all characters until the end of the character value expression are returned. NULL is returned when the string length is less than 1.
Specify SMALLINT or INTEGER as the data type for the start position and string length. Refer to "Table A.1 Data type combinations that contain literals and can be converted implicitly" in "A.3 Implicit Data Type Conversions" for information on data types that can be specified when a literal is specified.
The data type of the return value is TEXT.

Note

There are two types of SUBSTR. One that behaves as described above, and one that behaves the same as SUBSTRING. The search_path needs to be modified for it to behave the same as the specification described above.
It is recommended to set search_path in postgresql.conf. In this case, it will be effective for each instance. Refer to "9.2.1 Notes on SUBSTR" for information on how to configure postgresql.conf.
The configuration of search_path can be done at the user level or at the database level. Setting examples are shown below.
- Example of setting at the user level
  This can be set by executing an SQL command. "user1" will be used as the username in this example.
```
ALTER USER user1 SET search_path = "$user",public,oracle,pg_catalog;
```
- Example of setting at the database level
  This can be set by executing an SQL command. "db1" will be used as the database name in this example.
```
ALTER DATABASE db1 SET search_path = "$user",public,oracle,pg_catalog;
```
  You must specify "oracle" in front of "pg_catalog".
If the change has not been implemented, SUBSTR is the same as SUBSTRING.

See

Refer to "SQL Commands" under "Reference" in the PostgreSQL Documentation for information on ALTER USER and ALTER DATABASE.

Information

The general rules for SUBSTRING are as follows:

The starting position will be from the beginning of the character value expression, whether the starting position is positive, 0, or negative.
When string length is not specified, all characters until the end of the character value expression are returned.
An empty string is returned if the returned string is 0 or less or if the specified string length is less than 1.

See

Refer to "String Functions and Operators" under "The SQL Language" in the PostgreSQL Documentation for information on SUBSTRING.

Example

In the following example, part of the string "ABCDEFG" is extracted:

SELECT SUBSTR('ABCDEFG',3,4) "Substring" FROM DUAL;
 
 Substring
-----------
 CDEF
(1 row)

SELECT SUBSTR('ABCDEFG',-5,4) "Substring" FROM DUAL;

 Substring
-----------
 CDEF
(1 row)