Returns an integer representing the starting position of an expression.
charindex(expression1, expression2 [, start])
is a binary or character column name, variable, or constant expression. Can be char, varchar, nchar, nvarchar, unichar, univarchar, binary, text_locator, unitext_locator, image_locator or varbinary.
when specified, causes the search for expression1 to start at the given offset in expression2. When start is not given, the search start at the beginning of expression2. start can be an expression, but must return an integer value.
Returns the position at which the character expression “wonderful” begins in the notes column of the titles table:
select charindex("wonderful", notes)
from titles
where title_id = "TC3218"
----------- 46
This query executes successfully, returning zero rows. The column spt_values.name is defined as varchar(35):
select name from spt_values where charindex( 'NO', name, 1000 ) > 0
In comparison, this query does not use start, returning the position at which the character expression “wonderful” begins in the notes column of the titles table:
select charindex("wonderful", notes)
from titles
where title_id = "TC3218"
----------- 46
charindex, a string function, searches expression2 for the first occurrence of expression1 and returns an integer representing its starting position. If expression1 is not found, charindex returns 0.
If expression1 contains wildcard characters, charindex treats them as literals.
If expression2 is NULL, returns 0.
If a varchar expression is given as one parameter and a unichar expression as the other, the varchar expression is implicitly converted to unichar (with possible truncation).
If only one of expression1 or expression2 is a locator, the datatype of the other expression must be implicitly convertible to the datatype of the LOB referenced by the locator.
When expression1 is a locator, the maximum length of the LOB referenced by the locator is 16KB.
The start value is interpreted as the number of characters to skip before starting the search for varchar, univarchar, text_locator, and unitext_locator datatypes, and as the number of bytes for binary and image_locator datatypes.
The maximum length of expression1 is 16,384 bytes.
If a varchar expression is given as one parameter and a unichar expression as the other, the varchar expression is implicitly converted to unichar (with possible truncation).
ANSI SQL – Compliance level: Transact-SQL extension.
Any user can execute charindex.
Documentation Transact-SQL Users Guide
Function patindex
