Find a String within a String in SQL

Most of the major DBMSs provide us with a way to find a string within a string using SQL. By this, I mean use a SQL query to find the position of a substring within a string.

There are several SQL functions that allow us to do this, including INSTR(), LOCATE(), POSITION(), and CHARINDEX(). The function you use will depend on your DBMS, and possibly whether or not you need to specify a starting position.

Oracle

Oracle has an INSTR() function that returns the starting position of a given substring within a string.

Example:

SELECT INSTR('Big fat cat', 'fat')
FROM DUAL;

Result:

This shows us that the substring fat starts at position 5 in the string Big fat cat.

It’s possible to specify a starting position by passing a third argument. We can also pass a fourth argument to specify which occurrence to find:

SELECT INSTR('That fat cat', 'at', 1, 2)
FROM DUAL;

Result:

In this case I started the search at position 1 and searched for the second occurrence (by using 2 as the fourth argument).

Oracle also has an INSTRB() function that does a similar thing, except that it returns the position based in bytes.

MySQL

MySQL has an INSTR() function that does a similar thing to Oracle Database’s function of the same name:

SELECT INSTR('Cats and dogs like to run', 'dogs');

Result:

However, if you need to specify a starting position, you’ll need to use either LOCATE() or POSITION():

SELECT LOCATE('cat', 'One cat jumped over the other cat', 6);

Result:

Here, the first occurrence of cat begins at position 5, but I specified the search to start at position 6. Therefore, the position of the next occurrence of that string was the one that was returned.

Note that, although the search started at position 6, the function still returns the position of the substring within the string – not from the start position.

The POSITION() function is a synonym for the two-argument syntax of LOCATE(), but with a slightly different syntax (POSITION() doesn’t allow us to specify a starting position).

MySQL also has a REGEX_INSTR() function that returns the starting index of the substring of the string that matches the regular expression specified by the specified pattern.

MariaDB

Like MySQL, MariaDB also has an INSTR() function, as well as a a LOCATE() and POSITION() function:

SELECT 
    INSTR('No news is good news', 'news') AS "INSTR",
    POSITION('news' IN 'No news is good news') AS "POSITION",
    LOCATE('news', 'No news is good news') AS "LOCATE 1",
    LOCATE('news', 'No news is good news', 5) AS "LOCATE 2";

Result:

+-------+----------+----------+----------+
| INSTR | POSITION | LOCATE 1 | LOCATE 2 |
+-------+----------+----------+----------+
|     4 |        4 |        4 |       17 |
+-------+----------+----------+----------+

MariaDB also has a REGEX_INSTR() function that returns the starting index of the substring of the string that matches the regular expression specified by the specified pattern.

SQL Server

When it comes to SQL Server, the CHARINDEX() function is what we’re looking for:

SELECT CHARINDEX('Bob', 'Bob likes beer. Bob also likes beef.', 16);

Result:

In this case I used the optional third argument to specify a starting position.

SQL Server also has a PATINDEX() function that searches for a pattern within a string.

SQLite

SQLite has an INSTR() function to satisfy our needs:

SELECT INSTR('Black cat', 'lack');

Result:

PostgreSQL

In PostgreSQL, we need to use the POSITION() function:

SELECT POSITION('Break' IN 'Bangkok Breaking');

Result: