SQL Server CHARINDEX() Function

The CHARINDEX() function is one of the string functions in SQL Server, which is used to find the position of a substring in a string. If the substring is found, the function returns the starting position of the substring in the original string; otherwise, it returns 0.

Syntax

CHARINDEX( substring, string [, start_location ] )

Where:

• substring: the substring to be searched.
• string: the original string in which to search for the substring.
• start_location (optional): specifies the starting position of the search. Default is 1.

Use Cases

The CHARINDEX() function is commonly used for manipulating text data within strings, such as finding specific keywords, extracting substrings, etc.

Examples

Below are two examples of using the CHARINDEX() function:

Example 1

Assume we have a string that contains multiple email addresses, and we need to find the email address that contains “@gmail.com” and return its starting position.

SELECT CHARINDEX('@gmail.com', '[email protected]; [email protected]; [email protected]')

Output:

8

In the above example, we pass ‘@gmail.com’ as the substring to the CHARINDEX() function, and ‘[email protected]; [email protected]; [email protected]’ is the original string in which to search for the substring. The function returns the starting position of the substring in the original string, which is the position of the first ‘@’ character (counting from 1).

Example 2

Assume we have a string that contains a group of file names, each of which starts with a specific prefix, and we need to extract the file names.

DECLARE @str VARCHAR(100) = 'file1.txt, file2.pdf, file3.docx';
SELECT SUBSTRING(
    @str,
    CHARINDEX(' ', @str) + 1,
    CHARINDEX(',', @str, CHARINDEX(' ', @str)) - CHARINDEX(' ', @str) - 1
  ) AS FileName1,
  SUBSTRING(
    @str,
    CHARINDEX(',', @str, CHARINDEX(',', @str) + 1) + 2,
    LEN(@str) - CHARINDEX(',', @str, CHARINDEX(',', @str) + 1) - 1
  ) AS FileName2;

Output:

In the above example, we first use the CHARINDEX() function to find the starting position of the first file name. The function searches for the position of the first space character and returns its position plus 1 (which is the starting position of the first file name). Then, we use the CHARINDEX() function again to find the position of the first comma, which is the ending position of the first file name. Therefore, we can use the SUBSTRING() function to extract the first file name. For the second file name, we need to find the position of the second comma and start extracting the file name from the character after it.

Conclusion

The CHARINDEX() function is a simple and useful string function that can help us find the position of a substring in a string. Whether for data cleaning or data extraction purposes, this function is a valuable tool to have in our SQL toolkit.