This article will discuss a standard string operation: using Legacy and Standard SQL to find a substring within another string.
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:
We can use this function as shown below:
It should return:
As mentioned, the position index starts from 1 and not 0.
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:
Example usage of this function is as shown below:
STRPOS('linuxhint', 'hint') AS POSITION;
The query should return the position as shown:
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:
Note that the function uses the IN operator to specify which substring to search. This should return:
In MySQL, you can also use the locate() function to find the position of a substring. An example usage is shown below:
MS SQL Server
In SQL Server, we use the charindex() function to determine the position of a substring. An example usage is shown below:
In DB2, you can find the substring position using the posstr() function. An example is shown below:
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.