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 |
|---|---|
|
String expression |
|
Starting position (starts at 1) |
|
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 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):
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