CODEWITHSUNDEEP
sql
user1991499
user1991499

Reputation: 25

SQL query on field name

The names in my table appear like so,

Select fname-ab, fname-wxyz, lname-ab, lname-wxyz
From table

Is there a way to write a query that returns the results from all fields whose field name end with -ab

Upvotes: 1

Views: 783

Answers (2)

Eric J. Price
Eric J. Price

Reputation: 2785

This would work...

Declare @tableSchema Varchar(50) = 'dbo',
        @tableName Varchar(50) = 'table',
        @columns Nvarchar(Max),
        @sql Nvarchar(Max)

Select  @columns = Coalesce(@columns + ',','') + '[' + sc.name + ']'
From    syscolumns sc
Join    sysobjects so
        On  sc.id = so.id
Join    sys.schemas ss
        On  so.[uid] = ss.[schema_id]
Where   so.name = @tableName
And     ss.name = @tableSchema
And     sc.name like '%-ab'

Set     @sql = 'Select ' + @columns + '
                From    [' + @tableSchema + '].[' + @tableName + ']'

Exec    sp_executesql @sql

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270391

Most databases support and information_schema.columns table. You can do the following query to get all the columns that meet your criteria:

select column_name
from information_schema.columns
where table_name = 'table' and column_name like '%ab'

You can then put this list into Excel, create a formula to add a comma to the end, copy the list into a SQL interface, and fix the syntax (add select, add from, remove last comma from list).

This is actually easier to do than describe.

Alternatively, you can use some form of dynamic SQL, but the description of that depends on the database.

Upvotes: 3

Related Questions