Selecting column using REGEXP in MySQL

Question

So I have a table with many columns. Suppose that each column contains similar keywords, differing only by a few. I want to select these columns based on their similar keywords.

At first, this was my try:

SELECT * REGEXP 'pages_title$' FROM 'pages';

That is, any column that ends with pages_title should be selected. So the REGEXP should apply to the column's name, not any entries. Is this possible? All of the examples I have found online pertain to using REGEXP to isolate certain values within the table, which isn't what I want.

Answer 1

This isn't a complete answer but it might get something rolling. You can build your query dynamically:

declare @q varchar(1000)
set @q = 'select ' + @columnName + ' from table'
EXEC(@q)

Otherwise, you could get a selected set of column names from a table like so (MS T-SQL):

select name from DB.sys.syscolumns 
where id=(
    select id 
    from DB.sys.sysobjects 
    where xtype='U' 
    and name='pages'
) 
where name LIKE '%pages_title'

Not sure how to use this set to query your table for a specific set of columns. Perhaps you could combine these two approaches somehow?