SUBSTRING

the SUBSTRING function is used to extract a portion of a string based on specified starting position and length.

Note

Some systems call this function SUBSTR.

See also REGEXP_SUBSTR.

Syntax

SUBSTRING( expr, start_pos, length )

Arguments

Parameter

Description

expr

Original string expression from which you want to extract the substring

start_pos

Accepts an integer or bigint expression that specifies the position within the string where the extraction should begin. If start exceeds the number of characters in the expression, an empty string is returned. If start is less than 1, the expression starts from the first character

length

Accepts an integer or bigint expression that specifies the number of characters to be returned from the expression. If the sum of start and length exceeds the total number of characters in the expression, the entire value starting from the position specified by start is returned. If length is negative or zero, the function returns an empty string

Returns

  • Returns the same type as the argument supplied

  • If any of the arguments is NULL, the return is NULL

Notes

  • Character count starts at 1.

Examples

For these examples, assume a table named nba, with the following structure:

CREATE TABLE nba
(
   Name text(40),
   Team text(40),
   Number tinyint,
   Position text(2),
   Age tinyint,
   Height text(4),
   Weight real,
   College text(40),
   Salary float
 );

Here’s a peek at the table contents (Download nba.csv):

nba.csv

Name

Team

Number

Position

Age

Height

Weight

College

Salary

Avery Bradley

Boston Celtics

0.0

PG

25.0

6-2

180.0

Texas

7730337.0

Jae Crowder

Boston Celtics

99.0

SF

25.0

6-6

235.0

Marquette

6796117.0

John Holland

Boston Celtics

30.0

SG

27.0

6-5

205.0

Boston University

R.J. Hunter

Boston Celtics

28.0

SG

22.0

6-5

185.0

Georgia State

1148640.0

Jonas Jerebko

Boston Celtics

8.0

PF

29.0

6-10

231.0

5000000.0

Amir Johnson

Boston Celtics

90.0

PF

29.0

6-9

240.0

12000000.0

Jordan Mickey

Boston Celtics

55.0

PF

21.0

6-8

235.0

LSU

1170960.0

Kelly Olynyk

Boston Celtics

41.0

C

25.0

7-0

238.0

Gonzaga

2165160.0

Terry Rozier

Boston Celtics

12.0

PG

22.0

6-2

190.0

Louisville

1824360.0

Substring using fixed offsets

Get 4 characters, starting from the 4th character

nba=> SELECT SUBSTRING("Name", 4, 4) FROM nba LIMIT 5;
substring
---------
ry B
Cro
n Ho
. Hu
as J

Truncating strings

Trim a string to 10 characters

nba=> SELECT SUBSTRING("Name", 1, 10) FROM nba LIMIT 5;
substring
----------
Avery Brad
Jae Crowde
John Holla
R.J. Hunte
Jonas Jere