What is the SQL query to find the first occurrence of a substring in a string?

The specific SQL query to find the first occurrence of a substring within a string varies slightly depending on the database system you're using. Here are examples for some common databases:

MySQL:

In MySQL, you can use the LOCATE() or INSTR() function.

Example:

SELECT LOCATE('substring', 'string_to_search');
SELECT INSTR('string_to_search', 'substring');

Both queries will return the starting position of 'substring' within 'string_to_search'. If the substring is not found, they return 0.

SQL Server:

In SQL Server, you can use the CHARINDEX() function.

Example:

SELECT CHARINDEX('substring', 'string_to_search');

This returns the starting position of 'substring' within 'string_to_search'. If not found, it returns 0.

PostgreSQL:

In PostgreSQL, you can use the POSITION() or STRPOS() function.

Example:

SELECT POSITION('substring' IN 'string_to_search');
SELECT STRPOS('string_to_search', 'substring');

Both queries will return the starting position of 'substring' within 'string_to_search'. If the substring is not found, they return 0.

Oracle:

In Oracle, you can use the INSTR() function.

Example:

SELECT INSTR('string_to_search', 'substring') FROM DUAL;

This returns the starting position of 'substring' within 'string_to_search'. If not found, it returns 0.

Example in Practice:

Let's say you have a table named `employees` with a column named `email` and you want to find the position of "@" in each email address using MySQL:

SELECT email, LOCATE('@', email) AS at_position FROM employees;

These functions are essential for string manipulation and data extraction in SQL. Always refer to your specific database system's documentation for the most accurate and detailed information.

Learn more about this