Can Redshift SQL perform a case insensitive regular expression evaluation?

Question

The documentation says regexp_instr() and ~ are case sensitive Posix evaluating function and operator. Is there a Posix syntax for case insensitive, or a plug-in for PCRE based function or operator

Example of PCRE tried in a Redshift query that don't work as desired because of POSIX'ness.

select 
  A.target
, B.pattern
, regexp_instr(A.target, B.pattern) as rx_instr_position
, A.target ~ B.pattern as tilde_operator
, regexp_instr(A.target
, 'm/'||B.pattern||'/i') as rx_instr_position_icase
from
(      select 'AbCdEfffghi' as target 
 union select 'Chocolate' as target 
 union select 'Cocoa Latte' as target 
 union select 'coca puffs, delivered late' as target
) A
,
(      select 'choc.*late' as pattern 
 union select 'coca.*late' as pattern 
 union select 'choc\w+late' as pattern
 union select 'choc\\w+late' as pattern
) B

Answer 1

To answer your question: No Redshift-compatible syntax or plugins that I know of. In case you could live with a workaround: We ended up using lower() around the strings to match:

select
  A.target
, B.pattern
, regexp_instr(A.target, B.pattern) as rx_instr_position
, A.target ~ B.pattern as tilde_operator
, regexp_instr(A.target, 'm/'||B.pattern||'/i') as rx_instr_position_icase
, regexp_instr(lower(A.target), B.pattern) as rx_instr_position_icase_by_lower
from
(      select 'AbCdEfffghi' as target
 union select 'Chocolate' as target
 union select 'Cocoa Latte' as target
 union select 'coca puffs, delivered late' as target
) A
,
(      select 'choc.*late' as pattern 
 union select 'coca.*late' as pattern 
 union select 'choc\w+late' as pattern
 union select 'choc\\w+late' as pattern
) B