Sometimes we need just a portion of a string value in our data to display. There is a string function we use called SUBSTR()
. There is also an equivalent function called SUBSTRING()
. In this article, we cover using this substring function in SQL.
Preparation
Here is the SQL Fiddle used in this article. The schema is as follows:
create table names ( id INT, name VARCHAR(50), age INT, gender VARCHAR(50) ); insert into names (id, name, age, gender) values (1, 'Bret Starkings', 55, 'M'); insert into names (id, name, age, gender) values (2, 'Bobbye Eyckel', 76, 'F'); insert into names (id, name, age, gender) values (3, 'Barbie Veschi', 50, 'F'); insert into names (id, name, age, gender) values (4, 'Electra Blazewicz', 47, 'F');
We use the SUBSTR()
function to get back a portion of the name column. The syntax of the function is as follows:
SUBSTR(column_name, position, num_letters);
The function takes three parameters: the column or string we want to extrapolate the substring from, a one-based start position in the string (indexing that starts at 1 instead of 0), and the number of character length. If we do this on the name column, the SQL Statement comes out as:
SELECT name, SUBSTR(name, 1, 4) AS NAME_SUBSTRING, age, gender FROM names;
And the code results in:
name | NAME_SUBSTRING | age | gender |
Bret Starkings | Bret | 55 | M |
Bobbye Eyckel | Bobb | 76 | F |
Barbie Veschi | Barb | 50 | F |
Electra Blazewicz | Elec | 47 | F |
Estrella Borleace | Estr | 57 | F |
The third parameter in this function can be left off if you choose:
SELECT name, SUBSTR(name, 4) AS NAME_SUBSTRING, age, gender FROM names;
name | NAME_SUBSTRING | age | gender |
Bret Starkings | t Starkings | 55 | M |
Bobbye Eyckel | bye Eyckel | 76 | F |
Barbie Veschi | bie Veschi | 50 | F |
Electra Blazewicz | ctra Blazewicz | 47 | F |
Estrella Borleace | rella Borleace | 57 | F |
You can also use negative values for the position:
SELECT name, SUBSTR(name, -4) AS NAME_SUBSTRING, age, gender FROM names;
name | NAME_SUBSTRING | age | gender |
Bret Starkings | ings | 55 | M |
Bobbye Eyckel | ckel | 76 | F |
Barbie Veschi | schi | 50 | F |
Electra Blazewicz | wicz | 47 | F |
Estrella Borleace | eace | 57 | F |
As with any of these SQL functions, syntax might be slightly different depending on the type of database you are using. The general concept is the same though. Just remember the indexing starts on 1 instead of 0 for the position parameter and you’ll do great at building your queries.
About us: Career Karma is a platform designed to help job seekers find, research, and connect with job training programs to advance their careers. Learn about the CK publication.