sql select with column name like

Question

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

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

Answer 1

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.

Answer 2

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.

Answer 3

This will show you the table name and column name

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

Answer 4

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.

Answer 5

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

Answer 6

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;

Answer 7

You cannot with standard SQL. Column names are not treated like data in SQL.

If you use a SQL engine that has, say, meta-data tables storing column names, types, etc. you may select on that table instead.

Answer 8

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

Answer 9

It is possible by using SELECT * ILIKE which is Snowflake SQL language extension.

SELECT

ILIKE 'pattern'

Specifies that only the columns that match pattern should be included in the results.

In pattern, you can use the following SQL wildcards:

• Use an underscore (_) to match any single character.
• Use a percent sign (%) to match any sequence of zero or more characters.

To match a sequence anywhere within the column name, begin and end the pattern with %.

Matching is case-insensitive.

---

Using table structure from the question:

CREATE TABLE tab(a1 INT, a2 INT, a3 INT, b1 TEXT, b2 TEXT, b3 TEXT);

INSERT INTO tab(a1,a2,a3,b1,b2,b3)
VALUES (1,2,3,'a','b','c');

Query:

SELECT * ILIKE 'a%'
FROM tab;

Output: