Many RDBMSs have an INSTR() function that enables us to find a substring within a string. Some (such as MySQL and MariaDB) also have a LOCATE() function and a POSITION() function (also supported by PostgreSQL), that do a similar thing.

SQL Server doesn’t have an INSTR() function. Nor does it have a LOCATE() or POSITION() function. But it does have the CHARINDEX() function that does the same thing.

SQL Server also has the PATINDEX() function, which does a similar job to CHARINDEX().

The CHARINDEX() Function

Here’s an example of the CHARINDEX() function:

SELECT CHARINDEX('news', 'No news is good news');

Result:

4

The function accepts a third argument that allows us to specify where to start the search:

SELECT CHARINDEX('news', 'No news is good news', 5);

Result:

17

In this example, we started the search at position 5, which was after the start of the first occurrence of news, so it returned the position of the second occurrence.

The PATINDEX() Function

The PATINDEX() function is similar to CHARINDEX(), except that it allows us to search for a pattern, rather than a specific string.

Here’s an example to illustrate what I mean:

SELECT PATINDEX('%ew%', 'No news is good news');

Result:

5

Here’s what happens if I remove the wildcard characters:

SELECT PATINDEX('ew', 'No news is good news');

Result:

0

See PATINDEX()vs CHARINDEX() for more on the differences between these two functions.