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 |
|---|---|
|
Original string expression from which you want to extract the substring |
|
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 |
|
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,
Team TEXT,
Number TINYINT,
Position TEXT,
Age TINYINT,
Height TEXT,
Weight REAL,
College TEXT,
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