MySQL POSITION() Function

The POSITION() function in MySQL is used to search for a substring within a string and return the position of its first occurrence.

Syntax

POSITION(substring IN string)

Parameters

Definition and Usage

• Purpose: The POSITION() function returns the position of the first occurrence of a substring in a string.
  • If the substring is not found, it returns 0.
  • The function performs a case-insensitive search.
• Note: The POSITION() function is identical to the LOCATE() function.

Usage Examples

Example 1: Basic Usage

Search for the character "3" in the string "W3Schools.com" and return the position:

SELECT POSITION("3" IN "W3Schools.com") AS MatchPosition;

Output:

Explanation: The function finds the first occurrence of "3" at position 2 in the string.

Example 2: Searching for Substring

Search for the substring "COM" in the string "W3Schools.com", and return the position:

SELECT POSITION("COM" IN "W3Schools.com") AS MatchPosition;

Output:

Explanation: The function finds the first occurrence of "COM" starting at position 11 in the string.

Example 3: Searching in a Column

Search for the substring "a" in the CustomerName column and return its position for each customer:

SELECT POSITION("a" IN CustomerName) AS MatchPosition
FROM Customers;

Output (example data):

Explanation: The function returns the position of the first occurrence of "a" in the CustomerName for each row.

Technical Details

• Works In: From MySQL 4.0 onwards.
• Return Type: Returns an integer representing the position of the first occurrence of the substring. If the substring is not found, the function returns 0.
• Search Type: The search is case-insensitive.
• Indexing: The function uses 1-based indexing, meaning the first character of the string is at position 1.

Applications

1. Substring Search: The POSITION() function is particularly useful when you need to locate the position of a specific substring within a string.
2. Data Validation: It can be used to check if a certain substring exists within a string, which can be helpful in data validation tasks.
3. Parsing Strings: You can combine this function with other string functions (like SUBSTRING(), MID(), or LEFT()) to extract parts of strings based on specific conditions.

Key Notes

• The POSITION() function is similar to the LOCATE() function. Both can be used interchangeably for substring searching.
• The function returns 0 if the substring is not found, which makes it easy to detect missing substrings.
• It is case-insensitive, meaning it does not differentiate between uppercase and lowercase letters during the search.

The POSITION() function is a powerful tool for string manipulation in MySQL, making it easier to find the position of substrings and enabling complex string analysis tasks.