title: How to filter the results of a query in postgresql when using CAST and SUBSTRING (pgAdmin 4)
body: <p>I am new to SQL and have a table that has a string that is made of numbers and text (e.g., "10 Windows").  From this table, I need to extract out the "10" from the string and move it into a new column as an integer (I'm using pgAdmin4)</p>
  
  <p>I was able to write a query to parse out the numbers from the string and return them to a new column as an integer. </p>
  
  <pre><code>SELECT data1, CAST(SUBSTRING(data1 FROM '(\d+)') AS INTEGER) AS data1_num
  FROM events
  </code></pre>
  
  <p>From here, I can get the numbers from the string in data1 into a column named data1_num as an integer. </p>
  
  <p>However, if I want to filter to find say, all of the rows that where data1_num = 27, by the query below, I get an error.</p>
  
  <pre><code>SELECT data1, CAST(SUBSTRING(data1 FROM '(\d+)') AS INTEGER) AS data1_num
  FROM events
  WHERE data1_num = 27
  </code></pre>
  
  <p>Error:</p>
  
  <pre><code>ERROR:  column "data1_num" does not exist
  LINE 3: WHERE data1_num = 27
            ^
   ********** Error **********
  
   ERROR: column "data1_num" does not exist
   SQL state: 42703
   Character: 205
  </code></pre>
  
  <p>Am I missing something?</p>
  
  <p>I feel that this should work and even if there were zero rows where data1_num was 27, I should not get an error.</p>