POSITION Operator

Returns the position of a string value within another string value.

 

Syntax

 

POSITION(value IN value)

 

The POSITION operator has these parts:

 

Part

Description

value

The value of interest and the containing value.

 

Remarks

 

Use the POSITION operator to locate a string within another string. If the second value contains a substring that is equal to the first value the operator returns the starting location of that substring (one-based). If the second value contains several substrings equal to the first value the operator returns the starting location of the first such substring. If the second value contains no substrings equal to the first value, the operator returns 0.

 

The POSITION operator is case-sensitive.

 

Examples

 

This example uses the POSITION operator to select all products that typically ship in bottles:

 

SELECT * FROM [Products] WHERE POSITION("bottles" IN [Quantity Per Unit]) > 0;

 

This example uses the POSITION operator and the CAST operator to select all products the price of which is not an integer value of dollars (that is, which contains a decimal point in the price):

 

SELECT * FROM [Products] WHERE POSITION("." IN CAST([Unit Price] AS TEXT)) > 0;