PostgreSQL – POSITION Function
The POSITION function, also known as POSITION(substring IN string) or STRPOS(string, substring), is used to find the starting position of a substring within a given string. It returns the position of the first occurrence of the specified substring within the string. The position is a 1-based index, meaning the first character of the string is at position 1.
The syntax of the POSITION function is as follows:
POSITION(substring IN string) -- or STRPOS(string, substring)
where substring is the substring you want to find within the main string.
and string is the main string in which you want to find the substring.
Here’s an example of how you might use the POSITION function in a SQL query:
SELECT POSITION('lo' IN 'Hello, World!') AS position;
In this example, the result would be 4, as the substring 'lo' starts at position 4 within the string 'Hello, World!'.
If the substring is not found in the main string, the function will return 0.
SELECT POSITION('abc' IN 'Hello, World!') AS position;
In this case, the result would be 0.
Remember that the POSITION function is case-sensitive. If you want to perform a case-insensitive search, you can convert both the substring and the string to a common case using functions like LOWER or UPPER before using the POSITION function.
SELECT POSITION('LO' IN UPPER('Hello, World!')) AS position;
This would result in 4 because the uppercase conversion was performed on both the substring and the main string.