Menu
Amazon Redshift
Database Developer Guide (API Version 2012-12-01)

CHARINDEX Function

Returns the location of the specified substring within a string. Synonym of the STRPOS function.

Syntax

Copy
CHARINDEX( substring, string )

Arguments

substring

The substring to search for within the string.

string

The string or column to be searched.

Return Type

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.

Usage Notes

CHARINDEX returns 0 if the substring is not found within the string:

Copy
select charindex('dog', 'fish'); charindex ---------- 0 (1 row)

Examples

The following example shows the position of the string fish within the word dogfish:

Copy
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:

Copy
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.