SQL CHARINDEX() function searches for a substring inside a string starting from a specified location.
It returns the position of the substring found in the searched string, if substring is not found it returns zero.
SYNTAX
CHARINDEX(substring, string, startlocation)
substring is a substring to search in string.
string is a string to be searched.
startlocation is the position where the search will start and it is an optional.
Lets look at an example of CHARINDEX() function in SQL .
Using CHARINDEX function to get the position of character within string
Following statement uses CHARINDEX function, which returns the position of character ‘t’ within string ‘Microsoft SQL Server’.
DECLARE @stringtxt varchar(20) ='Microsoft SQL Server'
SELECT CHARINDEX('t', @stringtxt) AS CharPosition
Using CHARINDEX function to get the position of text within string
Following statement uses CHARINDEX function which returns the Position of text ‘SQL’ within string ‘Microsoft SQL Server’.
DECLARE @stringtxt varchar(20) ='Microsoft SQL Server'
SELECT CHARINDEX('SQL', @stringtxt) AS CharPosition
[Also Read SUBSTRING()]
It searches for the substring ‘SQL’ in the string ‘Microsoft SQL Server’, and returns the position of substring ‘SQL’ is 11.
Using CHARINDEX to search from specified position within string
You can also specify a search position in CHARINDEX to search for a text from specified position within string.
For this you need to pass a third argument startlocation value in CHARINDEX.
Following statement uses a CHARINDEX, and search for text within string from a specified search position 12.
DECLARE @stringtxt varchar(20) ='Microsoft SQL SQL Server'
SELECT CHARINDEX('SQL', @stringtxt, 12) AS CharPosition
As you can see, it returns substring ‘SQL’ now at 15th position as you have passed search position 12 in CHARINDEX function that means it starts search in the string ‘Microsoft SQL SQL Server’ from 12th position and finds the first substring ‘SQL’ at 15th position within string.
Also Read..
