CHARINDEX finds the starting position of a substring in another string.
If the substring is not found, a 0 is returned.
The position of the searched string starts at 1, not 0.
The example returns the starting position of 'York' inside 'New York'.
SELECT CHARINDEX('York', 'New York') AS Position
| Position |
|---|
| 5 |
CHARINDEX cannot be used with image, ntext, or text data types.
CHARINDEX if often used in combination with other functions: SUBSTRING, IIF, etc.
By default CHARINDEX searches are not case-sensitive.
Syntax of the CHARINDEX function.
CHARINDEX(substring, string [,position])
substring -- a string that represents the sequence to find.
string -- the string to be searched.
position -- optional. The search starting position. Default is 0.
| PRODUCT |
|---|
| Id |
| ProductName |
| SupplierId |
| UnitPrice |
| Package |
| IsDiscontinued |
SELECT ProductName,
IIF(CHARINDEX('bottle', Package) > 0, 'Bottles','n/a')
AS 'Is Bottled'
FROM Product
| ProductName | Is Bottled |
|---|---|
| Chai | n/a |
| Chang | Bottles |
| Aniseed Syrup | Bottles |
| Chef Anton's Cajun Seasoning | n/a |
| Chef Anton's Gumbo Mix | n/a |
 |
|
SELECT CHARINDEX('angeles', 'Los Angeles'
COLLATE Latin1_General_CS_AS) AS Position
| Position |
|---|
| 0 |
