title: "Match but exclude" in amazon Redshift's REGEXP_SUBSTR
body: <p>I have been reading <em>a lot</em> of questions and info on REGEX but haven't found anything conclusive yet.</p>
  
  <p>I am using <code>REGEXP_SUBSTR</code> in amazon redshift which according to the documentation uses the regex flavour POSIX. My understanding is that it is fairly basic and <em>cannot</em> do look aheads.</p>
  
  <p>Is there any way to match a pattern but exclude characters that come at the beginning or the end ?</p>
  
  <p>I am trying to parse a <code>user_agent</code> field in our database and this would really help.</p>
  
  <h2>Example</h2>
  
  <p>Sample string 1 : <code>'Mozilla/5.0 (iPhone; CPU iPhone...'</code><br>
  Match hoped: <code>iPhone</code>  </p>
  
  <p>Sample string 2: <code>'Mozilla/5.0 (Windows NT 10.0; Win64; ...'</code><br>
  Match hoped: <code>Windows NT 10.0</code>.  </p>
  
  <p>I am trying to match what is between the first parenthesis and the following semi-colon (<code>iPhone</code>) but <strong>exclude</strong> the parenthesis and semi-colon from the match.</p>
  
  <p>For the moment I am using <code>'\\(.*;'</code> but it's fairly basic and feel like it could easily return a wrong match.</p>
  
  <p>I understand I could couple the returning match with redshift's <code>substring</code> but doing so results in fairly messy and unpredictable code. I am wondering if it would be possible to have a regex match that return just the string needed.</p>
  
  <p>I know other flavours of regex that can use look aheads could solve this but they are not available in redshift as far as I know.</p>
  
  <p>Any help or pointers would be super appreciated. </p>