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

User · Answer

This will show you the table name and column name  select table name column name from information schema columns where column name like   breakfast

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[sql] sql select with column name like

Examples related to sql