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.

Nicollenicolson

No that doesn't make sense. The column names are fixed, why would you need a regex to select them. REGEXP works on value only. – Lignin

Well, the idea is that other columns are added later (with similar keywords). I'd like the code to work when I add other columns, instead of going in and accounting for the added column in the code. So this is just purely not doable? – Nicollenicolson

Yeah, this is doable by querying information_schema tables and constructing dynamic SQL. But I wouldn't recommend doing that. Fix your database design. – Evacuee

Can you not just return * to account for new columns in the future? Is the number of columns returned critical for performance? In most cases the overhead of making the call far out weighs the bandwidth in returning the row data. – Lignin

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?

Nilla

There are better ways to attain what you are doing, why not just select *? – Nilla

Answer 2

REGEXP is meant for WHERE part of the query - you cannot use it for selecting list of columns you want to fetch...

For your cause you would need programatically to fetch table details (e.g. DESC pages) and then using any programming language match and build the list of columns and use them in your query...

Kwangchowan