Menu
I have a table with column names a1,a2...,b1.b2....
How can I select all those with column names like a%?
614
The SQL LIKE OperatorThe LIKE operator is used in a WHERE clause to search for a specified pattern in a column. There are two wildcards often used in conjunction with the LIKE operator: The percent sign (%) represents zero, one, or multiple characters.
Description. The SQL LIKE condition allows you to use wildcards to perform pattern matching in a query. The LIKE condition is used in the WHERE clause of a SELECT, INSERT, UPDATE, or DELETE statement.
To get the column name of a table we use sp_help with the name of the object or table name. sp_columns returns all the column names of the object. The following query will return the table's column names: sp_columns @table_name = 'News'
SELECT * FROM INFORMATION_SCHEMA. COLUMNS WHERE COLUMN_NAME LIKE 'PREFIX%' ORDER BY TABLE_NAME; Query: SELECT * FROM INFORMATION_SCHEMA.
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.
128
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
