SQL CHARINDEX Function

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.

Example

#

The example returns the starting position of 'York' inside 'New York'.

SELECT CHARINDEX('York', 'New York') AS Position
Result:  1 record
Position
5

Using CHARINDEX

#

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

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.


More Examples

CHARINDEX, with IIF

PRODUCT
Id
ProductName
SupplierId
UnitPrice
Package
IsDiscontinued
Problem: List all products with a column that indicates bottle packaging.
SELECT ProductName, 
       IIF(CHARINDEX('bottle', Package) > 0, 'Bottles','n/a') 
       AS 'Is Bottled'
  FROM Product
Result:  78 records
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

CHARINDEX, case sensitive search

Problem: Perform a case-sensitive search.
SELECT CHARINDEX('angeles',  'Los Angeles' 
       COLLATE Latin1_General_CS_AS) AS Position
Result:  1 record
Position
0

You may also like



Guides


vsn 3.1