Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Select All column with a Function

Is this possible in any way that I apply function on all columns in a SELECT. For example,

SELECT LEN(t.*) FROM Table t;

The problem is that the table is dynamic with dynamic number of columns and I need to apply a function on evry column.

like image 344
Imran Qadir Baksh - Baloch Avatar asked Oct 10 '22 11:10

Imran Qadir Baksh - Baloch


1 Answers

No, you need some dynamic sql;

declare @table varchar(256) = 'the_table'
declare @sql nvarchar(4000) = ''

select 
    @sql += case @sql when '' then '' else ',' end + ' func(' +  quotename(column_name) + ') as ' + quotename(column_name)
from 
    information_schema.columns 
where 
    table_name = @table

set @sql = 'select' + @sql + ' from ' + @table 
exec(@sql)

which produces & executes

select func([fld1]) as [fld1], func([fld2]) as [fld2] ... from the_table
like image 94
Alex K. Avatar answered Oct 13 '22 12:10

Alex K.