SUBSTRING

Returns a substring of the input starting at start_pos.

Note

Some systems call this function SUBSTR.

See also REGEXP_SUBSTR.

Syntax

SUBSTRING( expr, start_pos, length )

Arguments

Parameter Description
expr String expression
start_pos Starting position (starts at 1)
length Number of characters to extract

Returns

Returns the same type as the argument supplied.

Notes

  • Character count starts at 1.
  • If the value is NULL, the result is NULL.

Examples

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

CREATE TABLE nba
(
   Name varchar(40),
   Team varchar(40),
   Number tinyint,
   Position varchar(2),
   Age tinyint,
   Height varchar(4),
   Weight real,
   College varchar(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