Sunday, August 30, 2020

CHARINDEX() in SQL Server with example

 This function searches for one character expression inside a second character expression, returning the starting position of the first expression if found.

CHARINDEX ( expressionToFind , expressionToSearch [ , start_location ] )

A character expression containing the sequence to find. Max length 8000 bytes

A character expression to search.

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. This is an optional parameter


  • The first position in string is 1.
  • If the expressionToFind is not found in expressionToSearch, the CHARINDEX function will return 0.
  • Search is not case sensitive

1) SELECT CHARINDEX('TO', 'Customer') AS MatchPosition;

Result: 4

(Substring is not case sensitive)

2) SELECT CHARINDEX('mer', 'Customer', 3) AS MatchPosition;

Result: 6

3) SELECT CHARINDEX('o', 'Rohit Techvlog', 3) AS MatchPosition;

Result: 13

(Since it started from 3rd position it returns the position of 2nd 'o')

4) SELECT CHARINDEX('z', 'Rohit Techvlog', 1) AS MatchPosition;

Result: 0

(No match found)

5) This function can be used in CASE statement

DECLARE @Name AS VARCHAR(100)= 'Find SQL articles on Rohit techvlog';


          WHEN CHARINDEX('rohit', @Name) > 0

           THEN 'Exists'

           ELSE 'Not Exists'

       END AS FindSubString;

Result: Exists

6) This function can be used in a output of existing data in a table.

select JobTitle,CHARINDEX('p', JobTitle) as position_of_p

from Employee


1 comment:

  1. Thank you so much for throwing light on such an important topic, not sure if you are interested in 3rd party product but ZappySys has very easy solution.

    SQL Server Load Soap API