Q:

Alternative for REGEXP_INSTR command output in Amazon RedShift

As you all are aware REGEXP_INSTR command in Oracle/PLSQL allows you to search a string for a regular expression pattern.  It also provides three parameters namely start_position, nth_appearance, return_option.  
Redshift also supports the same command but doesn't  support all the three parameters due to which the following query totally functioning in Oracle/PLSQL wouldn't function in Redshift. 
substr(some_string, REGEXP_INSTR(some_string, '/regex1/', 1,1,1), 15));
(redshift uses substring instead of substr)
In the query above REGEXP_INSTR returns the  position of the character after the occurrence of the pattern as we have set the return_option to 1. 
(i.e if a match ends at position 3 it will return 4.  Thus from position 4 , 15 characters  will be extracted from "some_string")
We can't set the return option to 1 in Redshift as the REGEXP_INSTR command doesn't support parameters.  I'm looking for alternative ways to return the next position value of where the match occurs.  

A:

In Oracle you could do:
substr(some_string, regexp_instr(some_string, '/regex1/', 1, 1, 0)
  + length(regexp_substr(some_string, '/regex1/', 1, 1)), 15)

The regexp_substr() gives you the part of the string that matched the same pattern as the regexp_instr() call; adding the length of that matched section to the instr location offsets the result by that much.  Which has the same effect as setting return-opt to 1, at least in this scenario. 
Not familiar with RedShift, but from the docs it looks like the same sort of thing should work:
substring(some_string, regexp_instr(some_string, '/regex1/', 1)
  + len(regexp_substr(some_string, '/regex1/', 1)), 15)

The position argument can be skipped in both versions as you're using the default value of 1, as can nth-occurrence argument in Oracle (now return-opt is the default zero too); but this will still not work if you don't want the first occurrence.  You'd need to modify the pattern to try to pick up a later occurrence.  Hopefully that isn't an issue though. 
You'll also need to check that happens (in both systems) if there is no match, or other edge cases.