Create Substrings of Strings in PostgreSQL

January 5, 2024 | by Arround The Web | No comments

Create Substrings of Strings in PostgreSQL

There are instances where you may want to extract a given section (substring) from a string. It could be that you are only interested in the given section and want to leave out the other section of the string in your output. Imagine a case where you have a full name such as “fname lname” as one string and you only want to extract the “fname” in your output. For that, you must use the PostgreSQL substring function. We prepared a detailed guide to follow, including examples to help you understand how you can create substrings of strings in PostgreSQL.

Examples on How to Create Substrings of Strings in PostgreSQL

The first thing we must check is the syntax.

SUBSTRING( string/column_name, start_position, length)

In the given syntax, you can specify the string that you want to create a substring or specify the column in your table. Next, you must specify the position in the string where you want the substring to start from. Lastly, specify the substring’s length or the string’s end position. Let’s have a few examples to see it in action.

Example 1: Specify the Substring Length

When you have your target string, you can set how long the substring should be. For instance, if you have your string as “Linuxhint” and you want to create your substring as “Linux”, execute the following command:

SELECT SUBSTRING('Linuxhint' FROM 1 FOR 5) AS username;

We are using the FROM keyword to specify our start position and the FOR keyword to specify the length of the substring. The “username” is the name that we give to our output.

We get the following output by executing the command. Notice how we got our desired substring as the output:

Suppose you want the substring created from a different start position in your string. For instance, if you want “hint” as the substring, you simply change the start position and the length.

For that, we execute our command as follows:

Example 2: Specify the Position of the 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 the string sections from the specified position to the end.

For this example, we have our string as “Hello Linuxhint”. To get “Linuxhint” as our substring without specifying its position, we only need to specify at what position we want to create the substring. For this case, we start FROM position 6. Thus, our command is as follows:

SELECT SUBSTRING(‘Hello Linuxhint’ FROM 6) AS username;

Example 3: Specify the Start and End Positions

Given a string, you can create a substring by specifying the start and end positions. This way, even if the string’s length is longer than the desired substring, it will only create it based on the specified start and end positions.

Using “Hello Linuxhint” as our string, we can create our substring as “Hello Linux” and omit the other sections by specifying the start and end positions as follows:

SELECT SUBSTRING('Hello Linuxhint', 1, 11) AS username;

No keyword is required for this case, only the start and end positions.

Example 4: Working with a PostgreSQL Table

It’s also possible to create a substring based on the values that 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. We would have our command as follows:

Note how, for each value in the column, the output is a substring of length 3 of the original string.

Let’s update the table with a full name in our name column. Our new table looks as follows:

Now, if we want to only extract the first section 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 must specify the start position. For the length of the substring, we set it to be the position in each string where there is a space.

The space marks the separation between the first and last names. Thus, our command checks the position where the space begins in the string. Then, select the substring from the first position to where it meets the space.

We run our command as follows:

SELECT order_id, SUBSTRING(name FROM 1 FOR POSITION( ‘ ‘ IN name) – 1) AS client_fname FROM customers;

We select the “order_id” and the substring, and our output appears as follows:

That’s how you can create substrings from strings in a PostgreSQL table.

Conclusion

PostgreSQL offers the substring function that lets the users create a substring using different criteria. Depending on your goal, you can specify the length of the substring or the start and end positions. The examples that are covered in this post will help you get comfortable with creating substrings in PostgreSQL. Keep practicing to grasp the concept.

Source: linuxhint.com