Returns the position of a substring within a specified string.
See CHARINDEX function and POSITION function for similar functions.
Syntax
STRPOS(string, substring )
Arguments
- string
-
The first input parameter is the
CHARorVARCHARstring to be searched. - substring
-
The second parameter is the substring to search for within the string.
Return type
- INTEGER
-
The STRPOS function returns an
INTEGERcorresponding to the position of the substring (one-based, not zero-based). The position is based on the number of characters, not bytes, so that multi-byte characters are counted as single characters.
Usage notes
STRPOS returns 0 if the substring is not found within the
string.
SELECT STRPOS('dogfish', 'fist');
+--------+
| strpos |
+--------+
| 0 |
+--------+Examples
To show the position of fish within dogfish, use the following example.
SELECT STRPOS('dogfish', 'fish');
+--------+
| strpos |
+--------+
| 4 |
+--------+The following example uses data from the SALES table in the TICKIT sample database. For more information, see Sample database.
To return the number of sales transactions with a COMMISSION over 999.00 from the SALES table, use the following example.
SELECT DISTINCT STRPOS(commission, '.'),
COUNT (STRPOS(commission, '.'))
FROM sales
WHERE STRPOS(commission, '.') > 4
GROUP BY STRPOS(commission, '.')
ORDER BY 1, 2;
+--------+-------+
| strpos | count |
+--------+-------+
| 5 | 629 |
+--------+-------+