SQL Server: CHARINDEX String Functions in SQL Server



CHARINDEX: CharIndex is responsible for search expression for another expression and returns its starting position if found. The search pattern cannot include wildcard characters. The second the argument is a character expression, you can use column name here if you are applying it on fetching records from the table for a 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: This is a character expression to be searched.

start_location: This 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 the search at a 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 to 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 the 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

NUnit : Introduction

There are several unit test tools in the market, but one of them is NUnit, the most popular tool for doing unit testing. So in this tutorial...