Updated March 4, 2023
Introduction to Redshift Substring
To extract required string from existing string then we use the Redshift Substring () function. The extraction of the sub string will be done based on the position mentioned in the syntax. Redshift Substring () function usually consists of three arguments. The three arguments are as string, position from where you want the sub string and the third argument is number of characters that you want to extract.
Syntax:
Syntax of the Substring () function is as below:
substring (String, starting_position, length_of_characters);
- String: We specify the string that we need to search and extract the string from.
- starting_position: This argument is used to specify the position. The position can start from 1. This field is based on characters and not bytes. We can also specify negative number as well.
- length_of_characters: This argument is used to extract the sub string from the mentioned position. This field is based on characters and not bytes. We can not specify negative number.
The return type of the Redshift Substring() function is varchar.
How does Redshift Substring () Works?
Below let us look how the Substring works in the Redshift:
Example #1
Code:
SELECT SUBSTRING ('Redshift SUBSTRING', 4, 6 ) AS OUTPUT;
Output:
Shift
Screenshot for the same:
Code:
SELECT SUBSTRING ('Redshift SUBSTRING',10) AS Redshift_OUTPUT;
Output:
SUBSTRING
Screenshot for the same:
Code:
SELECT SUBSTRING ('Redshift SUBSTRING TESTING THE FUNCTION',10) AS "Redshift OUTPUT";
Output:
RedshiftOutput
SUBSTRING TESTING THE FUNCTION
Screenshot for the same:
Let us try to extract the name form below string.
Code:
SELECT SUBSTRING ('My Name is Rose', 12, 4) AS Name;
Output:
Rose
Screenshot for the same:
Example #2
Code:
SELECT SUBSTRING ('This is to test the Redshift substring function', 20) AS OUTPUT;
Output:
Redshift Substring function
If we check the above example, we could see we haven’t specify the length of characters. So, the total characters will be displayed right from the position of 20.
Screenshot for the same:
Examples of Redshift Substring
Given below are the examples of Redshift Substring:
Let us create a table and apply substring function on it:
Code:
create table Redshift_substring_test
(
S_NO int,
CompanyName VARCHAR(50)
);
Insert data into the table:
insert into Redshift_substring_test values (1, 'corp HP company');
insert into Redshift_substring_test values (2, 'corp Microsoft company');
insert into Redshift_substring_test values (3, 'corp General Motors company');
insert into Redshift_substring_test values (4, 'corp General electric company');
insert into Redshift_substring_test values (5, 'corp Granite company');
insert into Redshift_substring_test values (6, 'corp Fedex company');
insert into Redshift_substring_test values (7, 'corp Yahoo company');
insert into Redshift_substring_test values (8, 'corp Ikea company');
Select the values from the above table:
select * from Redshift_substring_test;
Output:
| SNO | CompanyName |
| 1 | Corp HP company |
| 2 | Corp Microsoft company company |
| 3 | Corp General Motors company |
| 4 | Corp General electric company |
| 5 | Corp Granite company |
| 6 | Corp Fedex company |
| 7 | Corp Yahoo company |
| 8 | Corp Ikea company |
Screenshot for the same:
Now let us extract just the company name from the above “Company_name” column. Which means exclude the string “corporation” in the entire column.
Code:
select *, SUBSTRING(Companyname , 5) AS Organisation_name from Redshift_substring_test;
Output:
| s_no | Companyname | Organisation_name |
| 1 | corp HP company | HP company |
| 2 | corp Microsoft company | Microsoft company |
| 3 | corp General Motors company | General Motors company |
| 4 | corp General electric company | General electric company |
| 5 | corp Granite company | Granite company |
| 6 | corp Fedex company | Fedex company |
| 7 | corp Yahoo company | Yahoo company |
| 8 | corp Ikea company | Ikea company |
Screenshot for the same:
Code:
select *, SUBSTRING(CompanyName , -1, 6) AS Organisation from Redshift_substring_test;
Output:
| s_no | Companyname | Organisation_name |
| 1 | corp HP company | corp |
| 2 | corp Microsoft company | corp |
| 3 | corp General Motors company | corp |
| 4 | corp General electric company | corp |
| 5 | corp Granite company | corp |
| 6 | corp Fedex company | corp |
| 7 | corp Yahoo company | corp |
| 8 | corp Ikea company | corp |
Screenshot for the same:
Example #2
Now let us see another example for the sub string.
Code:
create table error_codes
(
Code_no int,
code_msg VARCHAR(25)
);
Insert values into the tables:
Code:
insert into error_codes values (1, 'Code: 45235 error');
insert into error_codes values (2, 'Code: 23235 error');
insert into error_codes values (3, 'Code: 46235 error');
insert into error_codes values (4, 'Code: 76235 error');
insert into error_codes values (5, 'Code: 98235 error');
insert into error_codes values (6, 'Code: 12235 error');
insert into error_codes values (7, 'Code: 76235 error');
insert into error_codes values (8, 'Code: 34235 error');
Now let us select the data:
Code:
select * from error_codes;
Output:
| Code_no | code_msg |
| 1 | Code: 45235 error |
| 2 | Code: 23235 error |
| 3 | Code: 46235 error |
| 4 | Code: 76235 error |
| 5 | Code: 98235 error |
| 6 | Code: 12235 error |
| 7 | Code: 76235 error |
| 8 | Code: 34235 error |
Screenshot for the same:
Now let us extract only the code value from the “code_message” column.
Code:
select *, SUBSTRING(code_msg, 6,6) as code from error_codes;
Output:
| Code_no | code_msg | code |
| 1 | Code: 45235 error | 45235 |
| 2 | Code: 23235 error | 23235 |
| 3 | Code: 46235 error | 46235 |
| 4 | Code: 76235 error | 76235 |
| 5 | Code: 98235 error | 98235 |
| 6 | Code: 12235 error | 12235 |
| 7 | Code: 76235 error | 76235 |
| 8 | Code: 34235 error | 34235 |
Screenshot for the same:
Recommended Articles
This is a guide to Redshift Substring. Here we discuss the introduction, how does redshift Substring () works? and examples respectively. You may also have a look at the following articles to learn more –
