CHARINDEX() in SQL

Returns the starting position of a substring within a string. Position is 1-based (first character is position 1).

Syntax

CHARINDEX(substring, string [, start_position])
-- Some DBMS use POSITION() or INSTR() instead

Return type

INTEGER

CHARINDEX() Function Example

SELECT CHARINDEX('World', 'Hello World');
-- Output: 7

What is CHARINDEX() in SQL?

The CHARINDEX() function in SQL returns the starting position of a specified substring within a given string. The position is 1-based, meaning the first character in the string is at position 1. This function is primarily used in SQL Server and Sybase, while other databases like MySQL and PostgreSQL use POSITION() or INSTR() for similar functionality. CHARINDEX() is useful for searching text, extracting specific portions of a string, or performing conditional checks based on substring locations. If the substring is not found, the function returns 0.

Parameters:

substring: The string to search for
string: The string to search within
start_position: Optional starting position for search

Example Use Cases:

-- Find @ in email addresses

SELECT CHARINDEX('@', email) AS at_position FROM users;
-- Output:
email                       at_position
------------------------   ------------
john.doe@gmail.com         9
mary.smith@yahoo.com       11
bob@outlook.com            4
sarah.jones@company.com    12
mike.b@example.com         6

-- Extract domain from email

SELECT SUBSTRING(email, CHARINDEX('@', email) + 1) AS domain 
FROM users;
-- Output:
email                       domain
------------------------   ------------
john.doe@gmail.com         gmail.com
mary.smith@yahoo.com       yahoo.com
bob@outlook.com            outlook.com
sarah.jones@company.com    company.com
mike.b@example.com         example.com

-- Validate string contains substring

SELECT * FROM products WHERE CHARINDEX('premium', description) > 0;
-- Output:
product_id    name              description                    matches
----------    ---------------   ---------------------------    -------
P001          Premium Laptop    Premium business laptop        ✓
P002          Gold Package      Premium membership plan        ✓
P003          Elite Watch       Premium stainless steel        ✓
P004          Pro Camera       Premium photo equipment        ✓
P005          Deluxe Set       Premium quality materials      ✓

Notes:

Behavior:Returns 0 if the substring is not found (1 in some DBMS), is case-sensitive in some DBMS, and start_position is optional.
Performance Considerations: Consider indexing for frequent searches
Version Info: Function name varies by DBMS
Deprecated/Recommended Alternatives: POSITION() or INSTR() in some DBMS