SQL Reference - POSITION

POSITION(substr IN str)

substr: This is a substring that is to be found in the source string. It represents the sequence of characters that you want to locate within the string.
str: This is the source string where the substring ‘substr’ will be searched. This represents the string that the function will sift through to find the specified sequence.

Example

SELECT POSITION('world' IN 'hello world') as Position;

Output

+----------+
| Position |
+----------+
|       7  |
+----------+

Explanation

The POSITION function in MySQL is used to find the position of a substring in a string. The example code returns the position of ‘world’ in ‘hello world’. As ‘world’ starts at the 7th character, the output is 7.

POSITION(substring IN string)

substring: This parameter represents the substring that the function will look for within the string. It’s a sequence of characters that might be part of the larger string.
string: This is the string within which the function will look for the substring. The function will return the position (an integer) of the first occurrence of the substring within this string, or 0 if the substring is not found.

Example

SELECT POSITION('world' IN 'Hello world');

Output

Explanation

The POSITION function in PostgreSQL is used to search for a substring within a string and returns the position of the first occurrence. In the given example, it’s searching for ‘world’ in ‘Hello world’ and returning the position as ‘7’.

POSITION(sub_string IN string)

sub_string: This is the smaller string that will be searched within the larger string. It could be a character or a group of characters or word(s).
string: This is the larger string within which the smaller string (sub_string) will be searched. These could either be a sentence, a paragraph, or even a single word.

Example

SELECT POSITION('world' IN 'Hello, world!') FROM dual;

Output

Explanation

The POSITION function in Oracle finds the occurrence of a substring in a string and returns the position of the first character of the substring. In this case, it returns 13 because the substring ‘world’ starts at the 13th position in the string ‘Hello, world!’.