Wildcard column selection? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Wildcard column selection?

Author

Topic

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2007-09-21 : 10:32:09

Is it possible to have some thing like:

SELECT columns beginning with 'a' from mytable

kinda thing?

X002548
Not Just a Number

15586 Posts

Posted - 2007-09-21 : 10:34:23

WHERE Col LIKE 'a%'

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2007-09-21 : 11:18:02

Not sure if I've explained myself correctly.

If I use

'SELECT TELEPHONE_NUMBER FROM PURCHASE_LEDGER'

I get a list of all the values in the telephone number field. But, if I didn't know there was a field called TELEPHONE_NUMBER but knew there was a field, or fields, that began with TEL how should I syntax that query please?

Kristen
Test

22859 Posts

Posted - 2007-09-21 : 11:23:00

This will give you the matching column names:


SELECT	COLUMN_NAME
FROM	INFORMATION_SCHEMA.COLUMNS
WHERE	    TABLE_NAME = 'PURCHASE_LEDGER'
	AND COLUMN_NAME LIKE 'TEL%'

Kristen

Kristen
Test

22859 Posts

Posted - 2007-09-21 : 11:26:57

[code]
DECLARE @strSQL nvarchar(4000)

SELECT @strSQL = COALESCE(@strSQL + ', ', 'SELECT ') + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'PURCHASE_LEDGER'
AND COLUMN_NAME LIKE 'TEL%'

SELECT @strSQL = @strSQL + ' FROM PURCHASE_LEDGER '

SELECT [Debug] = @strSQL

EXEC (@strSQL)
[/code]
Kristen

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2007-09-21 : 11:38:05

how in the WORLD do you NOT know your column names????????

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2007-09-21 : 12:13:26

Many thanks Kristen.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-24 : 01:53:25

quote:
Originally posted by DonAtWork

how in the WORLD do you NOT know your column names????????

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Denormalization

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources