Earn income with your data and sql skills
Sign up and we'll send you the best freelance opportunities straight to your inbox.
We're building the largest self-service freelancing marketplace for people like you.

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



Earn income with your data and sql skills
Sign up and we'll send you the best freelance opportunities straight to your inbox.
We're building the largest self-service freelancing marketplace for people like you.

Guides


vsn 3.1