Dec 24, 2014

SQL Server: CHARINDEX String Functions in SQL Server


CHARINDEX: CharIndex is responsible to search an expression for another expression and returns its starting position if found. Search pattern cannot include wildcard characters. The second argument is a character expression, you can use column name here if you are applying it on fetching records from table for specific search pattern match, in which Adaptive Server searches for the specified pattern.

Syntax:

CHARINDEX ( expressionToFind ,expressionToSearch , [start_location ] )

expressionToFind Is a character expression that contains the sequence to be found. xpressionToFind is limited to 8000 characters.

expressionToSearch: Is a character expression to be searched.

start_location: Is an integer or bigint expression at which the search starts. If start_location is not specified, is a negative number, or is 0, the search starts at the beginning of expressionToSearch.

For example

SELECT CHARINDEX('ensure', Summary)
FROM dbo.Document
WHERE ID = 3;
Second example
DECLARE @expressionToFind VARCHAR(50)='singh'
DECLARE @expressionToSearch VARCHAR(50)='dilip kumar singh'

SELECT CHARINDEX(@expressionToFind, @expressionToSearch);

Result

(No column name)

13



If you want to start search at specific location then you will be pass start_location the function see below.

DECLARE @expressionToFind VARCHAR(50)='singh'

DECLARE @expressionToSearch VARCHAR(50)='dilip kumar singh'
SELECT CHARINDEX(@expressionToFind, @expressionToSearch,6);
Result
(No column name)

13

It will skip 6 characters from starting means it will be start search from “kumar singh”

If you will put start_location 17 then what happen see
DECLARE @expressionToFind VARCHAR(50)='singh'
DECLARE @expressionToSearch VARCHAR(50)='dilip kumar singh'
SELECT CHARINDEX(@expressionToFind, @expressionToSearch,17);
Result -

(No column name)

0

Reason it will skip 17 characters from string, after 17 characters it will not found search pattern “singh”.

Note: CHARINDEX cannot be used with text, ntext, and image data types. 

Some useful link
PATINDEX in SQL Server