get column name matching regex pattern sql
<p>I would like to get all columns within a table where the columns name is <code>foo32</code>. The number <code>32</code> could be any number. I have the following query that works fine excepts naturally it matches columns that do not only contain a number.</p>

<pre><code>SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name =  'table1'
AND table_schema =  'database1'
AND column_name LIKE 'foo%'
</code></pre>

<p>Unfortunately the above query will match <code>foo12abc</code> and <code>fooCDE</code>. </p>

<p>I tried the following based on this page but it returned an empty result so something must be a miss.</p>

<pre><code>SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name =  'table1'
AND table_schema =  'database1'
AND column_name REGEXP 'foo[^0-9]'
</code></pre>

<p>Example table:</p>

<pre><code>foo1 | foo21 | fooABC | foo123ABC | foo19 | col12
_________________________________________________
// data ...
</code></pre>

<p>I would like the query to return columns <code>foo1</code>, <code>foo21</code> and <code>foo19</code></p>