In this PostgreSQL tutorial, you will learn:
What is Postgresql Substring?
Syntax
Examples
Matching Substrings with SQL Regular Expression
Matching Substrings Using pgAdmin
Syntax
The PostgreSQL substring function takes the following syntax:
Parameters
Examples
In this example, we want to extract the first 4 characters from the word Guru99: The command will return the following:
We did not specify the starting position, so the extraction of the substring start at position 1. 4 characters were extracted to return the above. The following example shows how to specify the starting position: The command will return the following:
We specified that the extraction of the substring should begin from position 1, and 4 characters should be extracted. Let us extract 99 from the string Guru99: The command will return the following:
We specified the starting position as 5. Since the number of characters to be extracted was not specified, the extraction ran to the end of the string. Here is another example: The command will return the following:
We have started extraction at position 5, and 2 characters have been extracted. Consider the Book table given below:
We want to get a rough idea about the name of each book. However, we can extract only the first 15 characters from the name column of the table: The command will return the following:
We now have a rough idea about the name of every book.
Matching Substrings with SQL Regular Expression
In PostgreSQL, we can extract a substring matching a specified POSIX regular expression. In this case, the substring function is used with the following syntax: or Here is an explanation of the above parameters: The string is the source string whose data type is varchar, char, string, etc. The matching_pattern is the pattern to be used for searching in the string.
Examples:
The command will return the following:
Our input string is your age is 22. In the pattern, we are searching for a numeric pattern in our string when this is found, the substring function should only extract two characters.
How to Matching Substrings Using pgAdmin
Now let’s see how the actions are performed using pgAdmin. The above queries where we don’t need a database can be executed directly from the query editor window. Just do the following: Step 1) Login your account. Login to your pgAdmin account. Step 2) On pgAdmin, Click the Query Tool icon.
The query editor window will be opened. Step 3) Type query. Type the following query on the editor window. Step 4) Execute query Click the Execute icon to execute the query.
Step 5) Query execution is done. It should return the following:
Example 2: It should return the following:
Here is the next example: It should return the following:
Example 3: It should return the following:
Now, let us run the example using the Book table of the Demo database: Step 1) Login to your pgAdmin account. Step 2)
From the navigation bar on the left- Click Databases. Click Demo.
Step 3) Type the query in the query editor: Step 4) Click the Execute button.
It should return the following:
We now have a basic idea of the name of every book.
Matching Substrings with SQL Regular Expression
To accomplish the same on pgAdmin, do the following: Step 1) Login to your pgAdmin account. Step 2) Click the Query Tool icon.
The query editor window will be opened. Step 3) Type the following query on the editor window. Step 4) Click the Execute icon to execute the query.
It should return the following:
Summary:
The PostgreSQL Substring function helps in extracting and returning only a part of a string. The first character of the string is at position 1. If the number of characters to be extracted from the string is not specified, the function will extract characters from the specified start position to the end of the string. If the number of characters to be extracted is specified, only that number of characters will be extracted.
Download the Database used in this Tutorial
