Returns the location of the specified substring within a string. Synonym of the STRPOS function.
CHARINDEX( substring, string )
The substring to search for within the string.
The string or column to be searched.
The CHARINDEX function returns an integer corresponding to the position of the substring (one-based, not zerobased). The position is based on the number of characters, not bytes, so that multi-byte characters are counted as single characters.
CHARINDEX returns 0 if the substring is not found within the
select charindex('dog', 'fish'); charindex ---------- 0 (1 row)
The following example shows the position of the string
select charindex('fish', 'dogfish'); charindex ---------- 4 (1 row)
The following example returns the number of sales transactions with a COMMISSION over 999.00 from the SALES table:
select distinct charindex('.', commission), count (charindex('.', commission)) from sales where charindex('.', commission) > 4 group by charindex('.', commission) order by 1,2; charindex | count ----------+------- 5 | 629 (1 row)
See STRPOS Function for details.