Examples of how to create substrings of strings in PostgreSQL
The first thing we should check is the syntax.
substring(string/column_name, start_position, length)
In the given syntax, you can specify the string you want to make a substring of or specify the columns in your table. Next, you need to specify the position in the string where you want the substring to start. Finally, specify the length of the substring or the last position of the string. Let's look at some examples to see this in action.
Example 1: Specify substring length
When you have your target string, you can determine how long the substring should be. For example, if your string is in the form “LinuxHint” and you want to make your substring as “Linux”, execute the following command:
select substring ('linuxhint' from 1 to 5) as username;
We are using the FROM keyword to specify our starting position and the FOR keyword to specify the length of the substring. “Username” is the name we give to our output.
On executing the command we get the following output. Notice how we got our desired substring as output:
Let's say you want to create a substring from a different start position in your string. For example, if you want the “hint” as a substring, you simply change the start position and length.
For that, we execute our order like this:
Example 2: Specify position of substring
Sometimes, you may have your string but you don't know the exact length of the substring. However, you can specify at what position to start creating the substring. The output displays all string sections from the specified position to the end.
For this example, our string is “Hello LinuxHint”. To get “LinuxHint” as our substring without specifying its position, we just need to specify at what position we want to create the substring. For this case, we start from position 6. Thus, our order is as follows:
select substring ('hello linuxhint' from 6) as username;
Example 3: Specify start and end positions
Given a string, you can create a substring by specifying a start and end position. This way, even if the length of the string exceeds the desired substring, it will create it only based on the specified start and end position.
Using “Hello LinuxHint” as our string, we can create our substring as “Hello Linux” and skip the other sections by specifying the start and end position as follows:
select substring('hello linuxhint', 1, 11) as username;
No keywords are needed for this case, only the start and end positions are needed.
Example 4: Working with PostgreSQL tables
It is also possible to create a substring based on the values you select from a given column in your table. For our example, we use the “Customers” table.
Let's say we are targeting the “cust_email” column and we want to create a substring by specifying the length. Our command will be as follows:
Note that, for each value in the column, the output is a substring of length 3 of the original string.
Let's update the table with the full name in our name column. Our new table looks like this:
Now, if we want to extract only the first fragment from the name column, which is the first name of each of our clients, creating a substring for the name column will do the trick. Here, we have to specify the start position. For the length of the substring, we set it as the position in each string where there is a space.
This space marks the separation between the first and last names. Thus, our command checks the position where the space starts in the string. Then, select the substring from the first position to where it meets the space.
We run our command like this:
select order_id, substring(1 from position(''in name') – 1) as client_fname from customers;
We select “order_id” and substring, and our output looks as follows:
This is how you can create substrings from strings in a PostgreSQL table.
conclusion
PostgreSQL provides the substring function that lets users create a substring using a variety of criteria. Depending on your goal, you can specify the length or start and end positions of the substring. The examples included in this post will help you get comfortable with creating substrings in PostgreSQL. Keep practicing to understand the concept.
