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
Some useful link
PATINDEX in SQL Server
