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 referred to as a substring to search from a string.
[string]: The string from which we want to search a substring.
How does PostgreSQL Position() function works?
- One(1) is the first position in the string.
- If POSITION function is unable to find the substring within the string, then the position function will return position as zero(0).
- 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 which will contain a number. In our case, this number is 12 because the string is “PostgreSQL is awesome” and the first is string position returned by the PostgreSQL POSITION function is at character number 12 which will be stored in position_of_is temporary variable.
Illustrate the result of the above statement by using the following snapshot.
- 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’ in order to understand the examples of this function in detail.
Consider the following CREATE TABLE statement in order 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;
1. Consider the following statement which finds the position of the substring ‘n’ 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’ by 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’ is appearing multiple times in the input string ‘This is a PostgreSQL’
Advantages of using Position() function in PostgreSQL
- The POSITION() function considers a string character case while searching for a substring which means it is case sensitive.s
- This function always returns the first instance of occurrence.
- We can find the required string position within a table row by using the POSITION function.
Conclusion
We hope from the above article you have understood 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
This is a guide to PostgreSQL POSITION(). Here we discuss how to use PostgreSQL POSITION() function along with the examples. You may also have a look at the following articles to learn more –
