Text copied!
CHARINDEX function
In SQL, CHARINDEX function is used to search the position number of a character or substring in a string. In case the substring is not found then it returns 0.
What is a String?
In SQL, A string is a data type used to store data that can contain combination of numbers, letters, whitespaces and symbols. In simple term, String is a word or a sequence of characters.
What is a Substring?
A substring is a consecutive sequence of characters or a portion of a string.
The syntax of the CHARINDEX function generally looks like this :
CHARINDEX(substring, string, start_position)
• Specify the 'substring' in single quotes ( ' ' ) or 'column' that you want to search in string.
• Specify the 'string' or 'column' in which you want to search for substring.
• Specify the 'starting position' from where the search will begin in the string. This is an optional parameter and if not specified, the search starts from the beginning of string.
Here's an example of how you might use the CHARINDEX function :
1. Let's assume we have a table named "[Employees]".
2. Let's assume you want to search the position number of substring 'EY' in Employee_Name column.
3. Run below SQL statement :
SELECT CHARINDEX('EY',Employee_Name)
FROM [Employees];
4. In above statement, CHARINDEX function is searching for the substring 'EY' in Employee_Name column.
It returns 0 for all rows except the third row, which contains the value 'Casey'. In this row the substring 'EY' is found starting from the fourth position resulting in a return value of 4.
Remember :
Using this function in a select statement won't modify the [Employees] table directly, but it will only be reflected in the select statement's output.