CHARINDEX Function - SQL Server to PostgreSQL Migration

In SQL Server, the CHARINDEX function returns the position of the specified substring in the string. In PostgreSQL, you can use the POSITION function.

Both CHARINDEX and POSITION functions return 0 if the substring is not found.

SQL Server:

  -- Find the position of 'ello'
  SELECT CHARINDEX('ello', 'Hello, world!');
  /* 2 */
 
  -- You can specify the starting position for search, but return the position in the original string(!)
  SELECT CHARINDEX('ello', 'Hello, hello, world!', 5);
  /* 9 */

PostgreSQL:

  -- Find the position of 'ello'
  SELECT POSITION('ello' IN 'Hello, world!');
  /* 2 */
 
  -- Expression with CONCAT, REPEAT and SUBSTRING to find substring starting at the specified position
  SELECT POSITION('ello' IN CONCAT(REPEAT(CHR(1), 4), SUBSTRING('Hello, hello, world!', 5)));
  /* 9 */

Note that when the starting position is specified, you cannot just use simple POSITION with SUBSTRING in PostgreSQL as you need to get the position from the beginning of the original string, and also return 0 if the substring is not found.

For more information, see SQL Server to PostgreSQL Migration.