PostgreSQL Substring Function

PostgreSQL is an open-source database that runs on all operating systems, i.e., Linux, Mac OS X, and Windows, etc. It is an object-relational database management system that helps run dynamic websites and applications. A substring contains characters in a sequence within a string. PostgreSQL substring function is useful in getting specific parts of a string by applying queries on strings and tables. This article will help you to learn this feature while manipulating both strings:

• Applying PostgreSQL substring function via SQL shell (psql)
• Applying PostgreSQL substring function using pgAdmin

Syntax

>> Substring (‘string’, strt, length);

Where string is the name of a string or column, strt is the starting point of a part that is to be extracted and length shows the character’s number in a substring.

Applying PostgreSQL Substring Function via Shell (psql)

After successful installation of PostgreSQL, you will be able to open psql. After defining the local-host and the database name, enter a password for further functionalities. i.e., Here Postgres is the database name.

Substring Function using FROM…FOR Options

EXAMPLE 1: On a string
We will apply a query on a string “database” to extract a substring “tabas”.

>> Select substring (‘Database’ from 3 for 5);

“From 3” means that the substring will start from the character on index 3; Whereas 5 denotes the length of a substring. The resulted substring after applying function is as follow:

EXAMPLE 2: On a table

We will create a table named “Book” having book id and title as attributes.

The same query will be applied to the table to extract a substring from the title in the “book” table.

>> Select title, substring (title from 4 for 9) from book;

This query will extract the substring from the column of title from a table named “book” starting from 4 indexes and lasting for 9 words. The results are as follow:

PostgreSQL Substring function using a column in a table

Let’s suppose we have a table named “student”, having attributes of id, name, class, and subject. We will apply the query on “subject” column without using FROM and FOR options. Instead, we will use simple numbers to identify the index in string and length of a substring. We will also define the resultant column name so that the substring will be shown in a particular column. Here “Abbreviation” is used as a resultant column name. So that the names of all subjects are abbreviated, respectively. The query is as follows.

>> Select subject, substring (“subject”, 1, 4) As Abbreviation from student;

In the resultant column, each subject name is reduced to 4 characters.

PostgreSQL Replace Substring Function

Replace () function will change the specific substring in a string with a new word. Let’s suppose we have a string “PostgreSQL is bad”. We will change substring “bad” with “good”. The following query will explain the syntax of replace function:

>> Select replace (‘postgresql is bad’, ‘bad’, ‘good’);

Substring with Regular Expression

This function uses a regular expression in a query to extract a particular portion i.e., ^, $, % and & etc. We will use these expressions in different ways. Examples are as follow:

EXAMPLE 1: By using “^” Expression:

“^” represents the ending point in which we want to end the substring extraction. Suppose there is a table named “Admin” having an admin name and email. We must make a substring of address part in the email from the email column. Substring will be the portion after the symbol “@” till the “.” in the email.

We will apply the regular expression query in the table above.

>> Select email, substring (email, ‘@[^.]*’) from Admin;

Substring is ended before point “.” Because of the expression “^”.

EXAMPLE 2: POSIX regular expression (“\”):

Syntax:

>> Substring (string from pattern);

Pattern is used to apply the expression in a query. In this query, the expression “\” is used. We have a string “PostgreSQL is a useful tool”, we will extract the word “tool” as a substring with the help of the expression. The query is as follow:

>> Select substring (‘postgresql is useful tool’ from ‘w*oo\w*’);

This query will work in a way to bring that particular word containing double “o” in it.

EXAMPLE 3: By using “%” and “#”
This function will use these two expressions to extract a substring from a string. We will use the word “Mathematics” to make a substring “them”. The appended query will help you to understand easily.

>> Select substring (‘Mathematics’ from ‘%# “T_ _M#”%’);

Applying PostgreSQL Substring Function Using pgAdmin

Now let’s use the queries in pgAdmin and understand how these functions work.

Step 1: Log in to your pgAdmin account.

Step 2: PostgreSQL workbench will be opened. In this, you will find a column on the right side of the home screen. Expand “database” and further expand your specified database, i.e., Postgres. Then right-click the database and select the query tool. For the execution of queries, we must have some data in the database. As we have performed queries in psql, our database has tables, etc.

Step 3

Example 1: We will apply a query.

>> Select substring (‘database’ from 3 for 5);

And then hit the execute icon to execute the query. The result will be appended below:

Example 2: We will apply a query on the table that will extract the first letter of a string as a substring in a column “name” of the table named as “student”.

>> Select name, class, substring (name, 1, 1) as initial from student;

Results are as follows; it shows the first character in all names of students.

Conclusion

From this article, you will be now able to understand PostgreSQL substring functions in psql and pgAdmin. We have seen how PostgreSQL helps in applying functions for obtaining substring and functions on the substring. We provided a small overview of queries extracting substring with help of different parameters. Now you will be able to use PostgreSQL commands for substrings by following the examples we have provided here.

from Linux Hint https://ift.tt/2Rj91se