title: Extract Ticket Number from a URL
body: <p>In Amazon Redshift, we capture the Ticket Number for Survey we do in our company in one of the responses of the survey question:</p>
  <p>Data</p>
  <pre><code>          question            response 
            Q10                 https://test-auth.xyz.com/login?next=%2FSSO%2Fredirect%3Fredirect_uri%3Dhttps%253A%252F%252Ft.corp.xyz.com%252FV511449992%252Foverview%26client_id%3Dhttps%253A%252F%252Ft.corp.xyz.com%253A443%26scope%3Dopenid%26response_type%3Did_token%26nonce%3Dc981fb4f2ce6dc11ed6f1dd9170ccca9f433db940a62648a5a4effad23916f55%26sentry_handler_version%3Dtestgateway%26response_mode%3Dquery%26use_sentry_key%3D1%26state%3D%252FV511449992%252Foverview&amp;noauth=1&amp;require_digital_identity=false
  
            Q10                 https://t.test.xyz.com/P58752112
            Q10                 V501167359
            Q10                 [P59355408]
  </code></pre>
  <p>Data I need:</p>
  <pre><code>         question           response 
           Q10                V511449992
           Q10                P58752112
           Q10                V501167359
           Q10                P59355408
  </code></pre>
  <p>My Query</p>
  <pre class="lang-sql prettyprint-override"><code>select 
      responseid,
      case WHEN len &lt; 9 THEN SUBSTRING(response,(REGEXP_INSTR( response,'%252FV' )) + 5,10) 
           ELSE regexp_substr(Q10,'[^\[][a-zA-z0-9]*[0-9]+[a-zA-Z0-9]*') 
           END  as Q10
  FROM
  (
      select 
          r1.response,
          r1.responseid,
          MAX(CASE WHEN questionid = 'Q10' 
                   THEN LEFT(regexp_substr(response,'[a-zA-z0-9]*[0-9]+[a-zA-Z0-9]*'),10) 
                   ELSE NULL END) Q10,
          LEN(MAX(CASE WHEN questionid = 'Q10' 
                       THEN LEFT(regexp_substr(response,'[a-zA-z0-9]*[0-9]+[a-zA-Z0-9]*'),10) 
                       ELSE NULL END))as len
      FROM Surveytable r1
      where r1.questionid = 'Q10'
        and response is not null
      group by r1.responseid, r1.response
  ) 
  </code></pre>
  <p>But First records is where its failing.</p>
  <p>Can someone please help?</p>