PostgreSQL POSITION()

Updated May 26, 2023

Introduction to PostgreSQL POSITION()

As we know PostgreSQL supports various data types like string, if we are working with string we might need to manipulate the string data, there are various functions to work with the string-like we might have to find the substring of any string. The PostgreSQL POSITION function is used to find the location or position of the substring within a string. By using the position function we can easily find the location of string-like if we have a string “PostgreSQL is awesome” and we wanted to find the location of the substring “awesome” inside the string.

Syntax

Consider the following syntax of the POSITION() function:

SELECT POSITION([SUBSTRING] IN [string]);

Explanation:

[SUBSTRING]: The input string is a substring to search from a string.

[string]: The string from which we want to search a substring.

How does PostgreSQL Position() function work?

1. One(1) is the first position in the string.
2. If the POSITION function cannot find the substring within the string, the position function will return the position as zero(0).
3. Consider the following example to understand the working of the PostgreSQL Position function.

SELECT POSITION('is' IN 'PostgreSQL is awesome') AS position_of_is

The above syntax creates a temporary variable of name position_of_is containing a number. In our case, this number is 12 because the string is “PostgreSQL is awesome,” the first string position returned by the PostgreSQL POSITION function is at character number 12, which will be stored in the position_of_is temporary variable.

Illustrate the result of the above statement by using the following snapshot.

4. This function returns us the first position or location of the occurrence of the substring within the string.

Examples to Implement Position() Function in Postgre SQL

We will create tables named ‘category’ to understand the examples of this function in detail.

Consider the following CREATE TABLE statement to create the category table.

CREATE TABLE category
(
category_id serial PRIMARY KEY,
category_name VARCHAR(80) NOT NULL
);

Now, we will insert some data in the ‘category’ table by using the INSERT TABLE statement:

INSERT INTO category(category_name)values
('furniture'),
('Electronics'),
('Cloths');

Illustrate the result of the above statement by using the following snapshot and SQL statement.

select * from category;

Output:

1. Consider the following statement, which finds the substring’ n’ position in the column ‘category_name’. 

SELECT category_id, category_name, POSITION('n' IN "category_name") FROM category;

Illustrate the result of the above statement by using the following snapshot.

2. Consider the string ‘PostgreSQL is awesome’ from which we will find out the position of the ‘awesome’ using the PostgreSQL POSITION function.

SELECT POSITION('awesome' IN 'PostgreSQL is awesome');

Illustrate the result of the above statement by using the following snapshot.

3. Consider the following example to understand the case sensitivity.

SELECT POSITION('AWESOME' IN 'PostgreSQL is awesome');

Illustrate the result of the above statement by using the following snapshot.

Here we can see that the result of position is zero (0), which means that the string AWESOME does not exist in the string ‘PostgreSQL is awesome’.

4. Consider the following example to understand if the string occurs multiple times.

SELECT POSITION('is' IN 'This is a PostgreSQL');

Illustrate the result of the above statement by using the following snapshot.

Here you can find the substring ‘is’ appears multiple times in the input string ‘This is a PostgreSQL.’

Advantages of using the Position() function in PostgreSQL

1. The POSITION() function considers a string character case while searching for a substring which means it is case sensitive.s
2. This function always returns the first instance of occurrence.
3. We can find the required string position within a table row using the POSITION function.

Conclusion

From the above article, we hope you understand how to use this function and how the POSITION() function works. Also, we have added several examples of the POSITION() function to understand it in detail.

Recommended Articles

We hope that this EDUCBA information on “PostgreSQL POSITION()” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

1. PostgreSQL Alias
2. Unique Key in MySQL
3. BETWEEN in SQL
4. PostgreSQL Features