PostgreSQL SUBSTRING() Function With Examples
Overview
In the field of relational databases, PostgreSQL stands as a powerful and versatile option. One of its key functions, the PostgreSQL SUBSTRING() function, empowers users to extract specific portions of text from strings. This article dives into the complexities of the SUBSTRING function in PostgreSQL, providing a comprehensive guide with syntax, parameter details, and numerous illustrative examples.
SUBSTRING Function in PostgreSQL
The SUBSTRING function in PostgreSQL is a vital function for manipulating textual data. It allows users to retrieve a portion of a string based on a specified starting point and, optionally, a specified length. This function is particularly useful for data preprocessing, text analysis, and data cleansing tasks.
Using the SUBSTRING function, you can extract any part of passed string value according to your needs.
Scaler Placement Report and Statistics
Scaler learners achieved 2.5x salary growth with average post-Scaler CTC reaching ₹23L.
Syntax
The syntax for the PostgreSQL SUBSTRING() function is as follows:
Transform Your Career
Choose from our industry-leading programs designed for career success
Modern Software and AI Engineering Program
Master full-stack development with AI integration
+1000 moreModern Data Science and ML with specialisation in AI
Advanced data science techniques with AI specialization
+1000 moreAdvanced AIML with Specialisation in Agentic AI
Deep dive into AIML with focus on Agentic systems
+1000 moreDevOps, Cloud & AI Platform Engineering
Build and manage AI-powered cloud infrastructure
+1000 moreAI Engineering Advanced Certification by IIT-Roorkee
Premier AI engineering certification from IIT-Roorkee
Parameters
The SUBSTRING() function requires two main parameters, with an optional third parameter:
- string: This is the input string from which you want to extract a substring.
- start: Specifies the position within the string where the extraction should begin. The position is a positive integer, with the first character being at position 1.
- length: (Optional) Defines the number of characters to extract from the string. If not provided, the function will extract all characters from the starting position to the end of the string.
Examples
Example 1:
Consider the following SQL query:
In this example, the query extracts a substring from the source string starting at position 14. The expected output is:
Output:
The SUBSTRING function initiates the extraction from the 14th position, which corresponds to the character p in the source string. It then proceeds to extract all subsequent characters until the end of the string, resulting in the output powerful.
Turn Learning into Career Growth
Example 2:
Let's examine another example:
Output:
The SUBSTRING function initiates the extraction from position , which is the character 'S' in the source string Scaler is amazing. The length parameter is set to , indicating that the function should extract a substring of characters.
The result is Scaler, representing the first characters of the source string. Notably, the character at position is included in the extracted substring.
Example 3:
Let's consider a case where we want to extract a portion of a URL:
Output:
In this example, the SUBSTRING function starts extracting from position 9 (which is the characer w in the input string https://www.example.com) and continues until the end of the string, providing the result www.example.com.
Example 4:
Now, let's extract a specific segment from a longer text:
Output:
In this case, the function begins at position 9 (i.e. the character 't') and extracts a substring with a length of 4 characters, resulting in 'this'.
Example 5:
Consider a scenario where we need to remove a prefix from a string:
Output:
In this example, the function starts at position 8 (i.e., the character 'T') and extracts the remaining part of the string, yielding 'Text'.
FAQs
Q. Can the SUBSTRING function in PostgreSQL extract characters from the end of the string?
A. Yes, by specifying a negative value for the start parameter, you can extract characters from the end of the string.
Q. How does the SUBSTRING function in PostgreSQL handle out-of-bounds indices?
A. If the start position is beyond the length of the string, the function returns an empty string.
Q. Can the length parameter be greater than the remaining characters in the string?
A. Yes, if the specified length parameter exceeds the number of characters remaining in the string from the start position, the function will extract all the remaining characters.
Conclusion
- The SUBSTRING function in PostgreSQL allows users to retrieve a portion of a string based on a specified starting point and, optionally, a specified length.
- This function is particularly useful for data preprocessing, text analysis, and data cleansing tasks.
- If the start position is beyond the length of the string, the function returns an empty string.
- By using the SUBSTRING function in PostgreSQL, users can enhance their data analysis, preprocessing, and data transformation tasks within the PostgreSQL environment.
Google
Amazon
Microsoft
Flipkart
Adobe1200+ more