SELECT only TEXT COLUMNs

Question

I want to select values of columns whose type is "text". Something like:

SELECT (SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'mytable' AND COLUMN_TYPE='text') FROM `mytable`

Is it possible? I have searched a lot but found nothing, for which I am little bit pessimistic.

[Ofcourse the issue in above statement is 2nd SELECT returns multiple rows]

#1242 - Subquery returns more than 1 row

Answer 1

You need to use a lot of statements here aside from the regular query not to mentioned you need to get the columns from Information.Schema. You can use GROUP_CONCAT() function along with PREPARE and EXECUTE statements.

And it will probably look like this:

SET @col = NULL;
SET @query = NULL;

SELECT (
   SELECT GROUP_CONCAT(COLUMN_NAME) as ColumnNames
   FROM INFORMATION_SCHEMA.COLUMNS
   WHERE table_name = 'table_name' 
   AND data_type='text' )
INTO @col;

SET @query = CONCAT('SELECT ', @col, ' FROM table_name');

PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

See my Sql Fiddle Demo.