[sql] sql select with column name like

I have a table with column names a1,a2...,b1.b2....

How can I select all those with column names like a%?

Thank you @Blorgbeard for the genious idea.

By the way Blorgbeard's query was not working for me so I edited it:

DECLARE @Table_Name as VARCHAR(50) SET @Table_Name = 'MyTable'          -- put here you table name
DECLARE @Column_Like as VARCHAR(20) SET @Column_Like = '%something%'    -- put here you element
DECLARE @sql NVARCHAR(MAX) SET @sql = 'select '

SELECT @sql = @sql + '[' + sys.columns.name + '],'
FROM sys.columns 
JOIN sys.tables ON sys.columns.object_id = tables.object_id
WHERE sys.columns.name like @Column_Like
and sys.tables.name = @Table_Name

SET @sql = left(@sql,len(@sql)-1) -- remove trailing comma
SET @sql = @sql + ' from ' + @Table_Name

EXEC sp_executesql @sql

This will show you the table name and column name

select table_name,column_name from information_schema.columns
where column_name like '%breakfast%'

This will get you the list

select * from information_schema.columns 
where table_name='table1' and column_name like 'a%'

If you want to use that to construct a query, you could do something like this:

declare @sql nvarchar(max)
set @sql = 'select '
select @sql = @sql + '[' + column_name +'],'
from information_schema.columns 
where table_name='table1' and column_name like 'a%'
set @sql = left(@sql,len(@sql)-1) -- remove trailing comma
set @sql = @sql + ' from table1'
exec sp_executesql @sql

Note that the above is written for SQL Server.

You need to use view INFORMATION_SCHEMA.COLUMNS

select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = my_table_name AND COLUMN_NAME like 'a%'

TO inline rows you can use PIVOT and for execution EXEC() function.

SELECT * FROM SysColumns WHERE Name like 'a%'

Will get you a list of columns, you will want to filter more to restrict it to your target table

From there you can construct some ad-hoc sql

Blorgbeard had a great answer for SQL server. If you have a MySQL server like mine then the following will allow you to select the information from columns where the name is like some key phrase. You just have to substitute the table name, database name, and keyword.

SET @columnnames = (SELECT concat("`",GROUP_CONCAT(`COLUMN_NAME` SEPARATOR "`, `"),"`") 
FROM `INFORMATION_SCHEMA`.`COLUMNS` 
WHERE `TABLE_SCHEMA`='your_database' 
    AND `TABLE_NAME`='your_table'
    AND COLUMN_NAME LIKE "%keyword%");

SET @burrito = CONCAT("SELECT ",@columnnames," FROM your_table");

PREPARE result FROM @burrito;
EXECUTE result;

Here is a nice way to display the information that you want:

SELECT B.table_catalog as 'Database_Name',
         B.table_name as 'Table_Name',
        stuff((select ', ' + A.column_name
              from INFORMATION_SCHEMA.COLUMNS A
              where A.Table_name = B.Table_Name
              FOR XML PATH(''),TYPE).value('(./text())[1]','NVARCHAR(MAX)')
              , 1, 2, '') as 'Columns'
  FROM INFORMATION_SCHEMA.COLUMNS B
  WHERE B.TABLE_NAME like '%%'
        AND B.COLUMN_NAME like '%%'
  GROUP BY B.Table_Catalog, B.Table_Name
  Order by 1 asc

Add anything between either '%%' in the main select to narrow down what tables and/or column names you want.