SQL Pocket Guide, 3rd Edition

String Functions

The following sections show how to use functions to perform common string operations.

Searching a String

In DB2 and Oracle, use the following version of INSTR to find the location of a substring within a string:

INSTR(string, substring[, position[, occurrence]])

You can specify a starting position for the search, and you can request that a specific occurrence be found. If position is negative, the search begins from the end of the string.

Note

In Oracle Database 10g and higher, you can also use REGEXP_INSTR, as described in the section Regular Expressions.

Oracle implements INSTR, INSTRB, INSTR2, and INSTR4, which work in terms of the input character set, bytes, Unicode code units, and Unicode code points, respectively. DB2 implements INSTR (and also INSTRB in version 9.7).

DB2 also supports the LOCATE and POSSTR functions:

LOCATE(substring, string[, position])
POSSTR(substring, string)

Both functions return the first occurrence of substring within string. Zero is returned if no match is found. The default is to search string beginning from character position 1.

In SQL Server, use the CHARINDEX function:

CHARINDEX(substring, string[, position])

The arguments are the same as they are for DB2’s LOCATE.

In MySQL, use either INSTR or LOCATE:

INSTR(string, substring)
LOCATE(substring, string[, position])

Use position to specify a starting character position other than 1. Zero is returned if substring is not found within string.

In PostgreSQL, use either POSITION or STRPOS:

POSITION(