Home » SQL CHARINDEX()

SQL CHARINDEX()

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 Postion of text ‘SQL’ within string

‘Microsoft SQL Server’.

DECLARE @stringtxt varchar(20) ='Microsoft SQL Server'

SELECT CHARINDEX('SQL', @stringtxt) AS CharPosition

It searches for the substring ‘SQL’ in the string ‘Microsoft SQL Server’, and returns the position of substring  ‘SQL’ is at 11th. 

Using CHARINDEX to search from specified position within string

You can also specify a serach 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.

Recommended for you

SUBSTRING()




Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.