PATINDEX Function - SQL Server to PostgreSQL Migration

In SQL Server, the PATINDEX function returns the position of the first occurrence of the pattern in the string. Depending on the database collation, the pattern search can be case-insensitive.

In PostgreSQL, you can use the REGEXP_INSTR function but note that the order of parameters and the pattern syntax are different.

SQL Server:

  -- Get the position of a letter
  SELECT PATINDEX('%[a-z]%', '12a45');
  # 3 
 
  -- Also finds capital 'A'
  SELECT PATINDEX('%[a-z]%', '12A45');
  # 3

PostgreSQL:

  -- Get the position of a letter (pattern and order of parameters are changed)
  SELECT REGEXP_INSTR('12a45', '[a-z]');
  # 3
 
  -- Does not work for capital 'A'
  SELECT REGEXP_INSTR('12A45', '[a-z]');
  # 0

For more information, see SQL Server to PostgreSQL Migration.