10.4.2 SUBSTR

Description: Extracts part of a string using characters to specify position and length.

Syntax

SUBSTR(str, startPos [, len ])

General rules

SUBSTR extracts and returns a substring of string str, beginning at position startPos, for number of characters len.
When startPos is positive, it will be the number of characters from the beginning of the string.
When startPos is 0, it will be treated as 1.
When startPos is negative, it will be the number of characters from the end of the string.
When len is not specified, all characters to the end of the string are returned. NULL is returned when len is less than 1.
For startPos and len, specify a SMALLINT or INTEGER type. When specifying literals, 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 the data types that can be 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 parameter must 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 "10.2.2 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. In this example, user1 is used as the username.
```
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. In this example, db1 will be used as the database name.
```
ALTER DATABASE db1 SET search_path = "$user",public,oracle,pg_catalog;
```
  You must specify "oracle" before "pg_catalog".
If the change has not been implemented, SUBSTR is the same as SUBSTRING.

See

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

Information

The general rules for SUBSTRING are as follows:

The start position will be from the beginning of the string, whether positive, 0, or negative.
When len is not specified, all characters to the end of the string are returned.
An empty string is returned if no string is extracted or len 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
-----------
(1 row)