MS SQL Server CHARINDEX() Function

Sometimes you may need to find the starting index or position of your specific expression into another expression in MS SQL Server. There is a function called CHARINDEX in MS SQL Server by which you can do this. CHARINDEX function searches for one character expression inside a second character expression, returning the starting position of the first expression if found.

Let’s see the function syntax:

CHARINDEX(expressionToFind, expressionToSearch [,start_location]);

In this syntax:

• In this function there are three parameters. Two are mandatory and third one is optional.
• Parameter expressionToFind is a character expression containing the sequence to find. expressionToFind has an 8000 character limit.
• Parameter expressionToSearch is a character expression to search.
• Parameter start_location  is an integer or bigint expression at which the search starts. If start_location is not specified, has a negative value, or has a zero (0) value, the search starts at the beginning of expressionToSearch.
• If either the expressionToFind or expressionToSearch expression has a Unicode data type (nchar or nvarchar), and the other expression does not, the CHARINDEX function converts that other expression to a Unicode data type. CHARINDEX cannot be used with image, ntext, or text data types.
• If either the expressionToFind or expressionToSearch expression has a NULL value, CHARINDEX returns NULL.
• If CHARINDEX does not find expressionToFind within expressionToSearch, CHARINDEX returns 0.
  CHARINDEX performs comparisons based on the input collation. To perform a comparison in a specified collation, use COLLATE to apply an explicit collation to the input.
• Position index is starting from 1. 0x0000 (char(0)) is an undefined character in Windows collations and cannot be included in CHARINDEX.
• Function returns bigint if expressionToSearch has an nvarchar(max), varbinary(max), or varchar(max) data type; int otherwise.
• Function returns the starting position of the first expression if found.

The following example uses CHARINDEX function to find the starting index or position of your specific expression into another expression.

DECLARE @doc varchar(500) = 'substring, char, uchar, charindex are very useful functions.';
SELECT CHARINDEX(@doc,'char') first_example,CHARINDEX(@doc,'char',17) AS second_example; 

Here is the result set.

I have an another post MS SQL Server String Related Functions where you will learn about different string related functions of MS SQL Server.

In this tutorial, I have shown you how to use CHARINDEX function in MS SQL Server. Hope you have enjoyed the tutorial. If you want to get updated, like my facebook page http://www.facebook.com/freetechtrainer and stay connected.