pattern matching in redshift

You can use the position or charindex functions.

dev=# select position('SUBSTR(' in 'jakjasf;kljksfjdsSUBSTR(kjasdkjadsjfjk');
 position 
----------
       18
(1 row)

dev=# select charindex('SUBSTR(', 'jakjasf;kljksfjdsSUBSTR(kjasdkjadsjfjk');
 charindex 
-----------
        18
(1 row)