What are INSTR Alternative Functions in Redshift?

The INSTR function in relational databases such as Oracle, Teradata, etc. is used to search a string for a substring and find the location of the substring in the string. The Amazon Redshift does not support the INSTR function. However, there are few string functions that you can use. In this article, we will check what are INSTR alternative functions in Redshift.

SQL INSTR String Function

The SQL INSTR function returns the location of a substring in a string. Optionally, you can provide the starting position and occurrence of the substring.

If a substring that is equal to substring is found, then the function returns an integer indicating the position of the first character of this substring. If no such substring is found, then the function returns zero.

Following is the syntax of INSTR function

INSTR( string, substring [, start_position [, th_appearance ] ] )

The following query returns the result 20 indicating the position of ‘ch’ in ‘chip’. This is the second occurrence of ‘ch’ with the search starting from the second character of the source string.

select INSTR('choose a chocolate chip cookie','ch',2,2) as pos;
  pos
-----
  20

SQL INSTR Alternative Functions in Redshift

Amazon Redshift support many useful string and regular expression functions, but INSTR function. However, you can use available string and regex functions as an INSTR alternative.

The following list represents the Redshift string functions that can replace the SQL INSTR function in Redshift.

REGEXP_INSTR
STRPOS
POSITION
CHARINDEX

Now, let us check these functions with examples.

Redshift REGEXP_INSTR as an INSTR Alternative

The REGEXP_INSTR function searches a string for a regular expression pattern and returns an integer that indicates the beginning position or ending position of the matched substring.

Consider following the example

select REGEXP_INSTR('choose a chocolate chip cookie','ch',2,2) as pos;
 pos
-----
  20
(1 row)

The REGEXP_INSTR is a best INSTR function alternative in Amazon Redshift. You can use this function if your requirement is to provide multiple parameters such as position and occurrence.

Redshift STRPOS as an INSTR Alternative

The STRPOS function returns the position of a substring within a specified string. The function works well for simple string search but not suited for advance search such as providing position and occurrences.

For example,

select STRPOS('choose a chocolate chip cookie','late') as pos;
 pos
-----
  15
(1 row)

Use REGEXP_INSTR function for more advance substring search.

Redshift POSITION as an INSTR Alternative

The Redshift POSITION function returns the location of the specified substring within a string. This function is a synonym of the STRPOS function.

For example,

select POSITION('late' IN 'choose a chocolate chip cookie') as pos;
 pos
-----
  15
(1 row)

Use REGEXP_INSTR function for more advance substring search.

Redshift CHARINDEX as an INSTR Alternative

The Redshift CHARINDEX function returns the location of the specified substring within a string. This function is a synonym of the STRPOS and POSITION functions.

For examples,

 select CHARINDEX('late' , 'choose a chocolate chip cookie') as pos;
 pos
-----
  15
(1 row)

Use REGEXP_INSTR function for more advance substring search.

Hope this helps 🙂