SQL Charindex

String and string operations are universal features across all databases. Therefore, it is nearly impossible to have a non-trivial database without the use of string and text information.

This article will discuss a standard string operation: using Legacy and Standard SQL to find a substring within another string.

Legacy SQL

We can find the position of a substring in Legacy SQL using the instr() function.

The function takes a string and a substring as the arguments. It then returns a one-based index position denoting the first occurrence of the substring.

If the substring is not found, the function returns 0.

The function syntax is as expressed below:

INSTR('string', 'substring');

We can use this function as shown below:

SELECT INSTR('linuxhint', 'hint') AS POSITION;

It should return:

POSITION
6

As mentioned, the position index starts from 1 and not 0.

Standard SQL

In Standard SQL, we use the STRPOS() function to get the location of a substring. The function takes two arguments: the string and the subtring.

It then returns a 1-index based position of the first occurrence of the substring. If the substring is not found, the function returns 0.

The syntax is as shown below:

STRPOS(string, SUBSTRING);

Example usage of this function is as shown below:

SELECT
STRPOS('linuxhint', 'hint') AS POSITION;

The query should return the position as shown:

POSITION
6

Database Engine Specific

Various database engines provide different functions for determining the position of a substring. Although the functionality remains the same, the function naming is different on various database engines.

PostgreSQL & MySQL

Both PostgreSQL and MySQL use the position() function to get the location of a substring. An example usage is as shown:

SELECT POSITION('hint' IN 'linuxhint') AS POSITION;

Note that the function uses the IN operator to specify which substring to search. This should return:

POSITION
----------
6
(1 ROW)

In MySQL, you can also use the locate() function to find the position of a substring. An example usage is shown below:

mysql> SELECT LOCATE('hint', 'linuxhint');
-> 6

MS SQL Server

In SQL Server, we use the charindex() function to determine the position of a substring. An example usage is shown below:

SELECT CHARINDEX( 'hint', 'linuxhint' );
-> 6

IBM DB2

In DB2, you can find the substring position using the posstr() function. An example is shown below:

SELECT POSSTR('hint', 'linuxhint');
-> 6

Conclusion

In this article, we explored various methods and techniques for locating the position of a substring in Legacy and Standard SQL. We also discussed each variation of the methods for various database engines.

To learn more about Standard and Legacy SQL, stay tuned for linuxhint articles.

SQL Charindex

Legacy SQL

Standard SQL

Database Engine Specific

Conclusion

About the author

John Otieno