Column selection using REGEXP in MySQL

Question

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

At first it was my attempt:

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

Thus, any column that ends with pages_title should be selected. Therefore, REGEXP should be applied to the column name, and not to any elements. Is it possible? All the examples I found on the Internet relate to using REGEXP to isolate certain values ​​in a table, which I don't want.

Answer 1

REGEXP is for the WHERE part of the query — you cannot use it to select the list of columns that you want to receive ...

For your purpose, you will need to programmatically obtain the table data (for example, DESC pages ), and then use any correspondence of the programming language and build a list of columns and use them in your query ...

Answer 2

This is not a complete answer, but it can do something. You can build your query dynamically:

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

Otherwise, you can get the selected set of column names from the table (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 a table for a specific set of columns. Perhaps you could somehow combine these two approaches?