T-SQL Tutorial T-SQL Advanced Database Management T-SQL References

T-SQL CHARINDEX() Function



The T-SQL (Transact-SQL) CHARINDEX() function returns the location of the first occurrence of a specified substring in a given string. If the substring is not found within the given string, this function returns 0.

This function performs a case-insensitive search. Note that the first position in string starts with 1.

Syntax

CHARINDEX(substring, string, start_position)

Parameters

substring Required. Specify the substring to search for in string.
string Required. Specify the string to search.
start_position Optional. Specify the starting position for the search. Default is position 1.

Return Value

Returns the location of the first occurrence of the substring in the string.

Example 1:

The example below shows the usage of CHARINDEX() function.

SELECT CHARINDEX('A', 'AlphaCodingSkills.com');
Result: 1

SELECT CHARINDEX('Coding', 'AlphaCodingSkills.com');
Result: 6

SELECT CHARINDEX('.com', 'AlphaCodingSkills.com');
Result: 18

SELECT CHARINDEX('l', 'AlphaCodingSkills.com');
Result: 2

SELECT CHARINDEX('l', 'AlphaCodingSkills.com', 5);
Result: 15

SELECT CHARINDEX('Z', 'AlphaCodingSkills.com');
Result: 0

SELECT CHARINDEX('ABC', 'AlphaCodingSkills.com');
Result: 0

Example 2:

Consider a database table called Employee with the following records:

PhoneNumberEmpIDAddress
+1-80540980001Brooklyn, New York, USA
+33-1479961012Grenelle, Paris, France
+31-2011503193Geuzenveld, Amsterdam, Netherlands
+86-10997324584Yizhuangzhen, Beijing, China
+65-672348245Yishun, Singapore
+81-3577990726Koto City, Tokyo, Japan

In the query below, the CHARINDEX() function is used to extract the country code from the PhoneNumber column records.

SELECT *, SUBSTRING(PhoneNumber, 1, CHARINDEX('-', PhoneNumber) - 1) AS CountryCode 
FROM Employee;

This will produce the result as shown below:

PhoneNumberEmpIDAddressCountryCode
+1-80540980001Brooklyn, New York, USA+1
+33-1479961012Grenelle, Paris, France+33
+31-2011503193Geuzenveld, Amsterdam, Netherlands+31
+86-10997324584Yizhuangzhen, Beijing, China+86
+65-672348245Yishun, Singapore+65
+81-3577990726Koto City, Tokyo, Japan+81

❮ T-SQL Functions