In this step by step SQL Tutorial I will show you how to use SQL CHARINDEX function. For the purpose of this tutorial I will use SQL Server 2012.
Other Versions of SQL Server:
- SQL Server 2012
- This blog post has been written using SQL Server 2012
- SQL Server 2008 / R2
- The examples we used should work in SQL Server 2008 and R2
- SQL Server 2005
- We suspect that this will work in SQL Server 2005 as well
We can use CHARINDEX() function when we need to find position of a character(s) in a string (field).
CHARINDEX Function - Is used to find position of a character(s) in a string (field).
CHARINDEX (<<ValueToFind>>, <<InputValue>>)
CHARINDEX (<<ValueToFind>>, <<InputValue >>,<<StartingPosition>>)
<<ValueToFind>> - Provide string value that contains the sequence to be found. String value which we want to find is limited to 8000 characters.
<<InputValue>> - Provide string value (field name or expression) that will be used as your input.
<<StartingPosition>> - String position where the search for <<StringToFind>> should start.
In the example below I will show you how to find '@' in EmailAddress. In this example I omit starting position argument (3rd argument) so this function will start searching for the specified character from the very beginning.
See below T-SQL code and results.
In the second example I want to find '@' in the EmailAddress field and the starting position is 7 (let say first part of email address must be minimum 6 characters). So the first seven characters are omitted during search for '@' character and the result are below.
In first email address we get 0 as @ does not exist AFTER 6th (starting 7th) character.
NOTE: Although we start search at 7th character the result of CHARINDEX is number of characters from the very beginning (not 7th position).
I hope that helps