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.
|
1 2 3 4 5 6 7 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
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; |