SQL-Select colums beginning with 'E'

[milleg1]

I have a table with a large number of columns beginning with letter E ... eg

ID,Date,Comment,E123,E124,E126,E156,F3,F5,G6,H90, etc

Is it possible to have a Select statement where I can choose all columns beginning with E, rather than having to type every column name in the Select statement??

 

[simian336]

2 options...
You could do something like

select *
from sys.columns
where object_id=object_id('dbo.table')
and name like 'E%'

And build and sp to delimite it.

OR in SSMS right click your table and choose SCRIPT TABLE AS > SELECT TO > NEW QUERY EDITOR WINDOW....

Simi

 

[bborissov]

DECLARE @sql varchar(8000)
SET @sql = 'SELECT ID,Date,Comment'

SELECT @sql = @sql + ','+Column_Name
FROM Information_Schema.Columns
WHERE Table_Name= 'YourTable' AND Column_Name LIKE 'E%'


SET @sql = @sql + ' FROM YourTable'

EXEC(@sql)

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.