LOCATE Function in SQL

The LOCATE function in SQL returns the position of the first occurrence of a substring within a string. If the substring is not found, it returns 0.

? Syntax:

LOCATE(substring, string [, start_position])

• substring: The part you're looking for.

• string: The full text to search within.

• start_position (optional): Where to start the search (default is 1).

? Example 1: Basic Usage

SELECT LOCATE('lo', 'Hello World') AS position;

Result:

? Example 2: With Starting Position

SELECT LOCATE('o', 'Hello World', 6) AS position;

Result:

? Notes:

• Returns 0 if the substring is not found.

• Case-sensitive in MySQL.

• In Oracle, use INSTR() instead.

• In SQL Server, use CHARINDEX().

Let me know if you'd like examples in other SQL dialects!