MySQL Regex Column Name Selection

If you’re like me, or if you’re not you might be at some point in your life… You may have a reason to lazily select all columns where the column name matches a pattern of sorts.

In my particular case, the data was organized in a fashion where several columns had the same name, but were also numbered. Since there were about 50 columns with the same pattern, I didn’t feel like writing out each column name.

SELECT CONCAT('SELECT ', 
    GROUP_CONCAT(COLUMN_NAME SEPARATOR ', '),
    ' FROM ', 'test_db.test_table')
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = 'test_db' 
    AND TABLE_NAME = 'test_table'
    AND COLUMN_NAME RLIKE '^FieldDesc';

SELECT CONCAT('SELECT ',

GROUP_CONCAT(COLUMN_NAME SEPARATOR ', '),

' FROM ', 'test_db.test_table')

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_SCHEMA = 'test_db'

AND TABLE_NAME = 'test_table'

AND COLUMN_NAME RLIKE '^FieldDesc';

And this outputs the select statement for you

SELECT DISTINCT FieldDesc6, FieldDesc7, FieldDesc8, 
                FieldDesc9, FieldDesc10, FieldDesc11, 
                FieldDesc12, FieldDesc13, FieldDesc14, 
                FieldDesc15, FieldDesc16, FieldDesc17, 
                FieldDesc18, FieldDesc19, FieldDesc20, 
                FieldDesc21, FieldDesc22, FieldDesc23,
                FieldDesc24, FieldDesc25, FieldDesc26, 
                FieldDesc27, FieldDesc28, FieldDesc29, 
                FieldDesc30, FieldDesc31, FieldDesc32, 
                FieldDesc33, FieldDesc34, FieldDesc35, 
                FieldDesc36, FieldDesc37, FieldDesc38, 
                FieldDesc39, FieldDesc40, FieldDesc41, 
                FieldDesc42, FieldDesc43, FieldDesc44, 
                FieldDesc45, FieldDesc46, FieldDesc47, 
                FieldDesc48, FieldDesc49, FieldDesc50, 
                FieldDesc51, FieldDesc52, FieldDesc53, 
                FieldDesc54, FieldDesc55, FieldDesc56 
                  FROM test_db.test_table;

SELECT DISTINCT FieldDesc6, FieldDesc7, FieldDesc8,

FieldDesc9, FieldDesc10, FieldDesc11,

FieldDesc12, FieldDesc13, FieldDesc14,

FieldDesc15, FieldDesc16, FieldDesc17,

FieldDesc18, FieldDesc19, FieldDesc20,

FieldDesc21, FieldDesc22, FieldDesc23,

FieldDesc24, FieldDesc25, FieldDesc26,

FieldDesc27, FieldDesc28, FieldDesc29,

FieldDesc30, FieldDesc31, FieldDesc32,

FieldDesc33, FieldDesc34, FieldDesc35,

FieldDesc36, FieldDesc37, FieldDesc38,

FieldDesc39, FieldDesc40, FieldDesc41,

FieldDesc42, FieldDesc43, FieldDesc44,

FieldDesc45, FieldDesc46, FieldDesc47,

FieldDesc48, FieldDesc49, FieldDesc50,

FieldDesc51, FieldDesc52, FieldDesc53,

FieldDesc54, FieldDesc55, FieldDesc56

FROM test_db.test_table;