SQL - How to select a table column using a wildcard?

[Bobalude]

I am a beginner with SQL and using MS Access. I've tried googling but can't seem to find the answer to this.

There's a table I want to select a group of columns from, and I want to use a partial wildcard to select the columns. I want to write something like

SELECT *suffixA FROM sampletable;

where * is my wildcard to capture any type of prefix column that ends with "suffixA".

From what I have seen, the LIKE command in SQL is only used after WHERE and filters the actual values inside the columns. It is not used to select the columns.

Is this possible?

 

[Markbnj]

It's possible, but not with any static syntax I'm aware of (not to say there is no such syntax, but I am not aware of it). You could do it using dynamic sql and the system objects tables (to get the list of columns), but really it would be a lot cheaper just to select everything and discard what you don't want on the client.

 

[presidentender]

Mark's right that there is probably a better way to do this. However, you can do:

SELECT * FROM information_schema.columns WHERE table_name = 'foo' AND column_name LIKE '%suffix'

This will get you a list of the column names.

EDIT: That works in MSSQL Server. I don't know if Access will be happy with it.

 

[manlymatt83]

I would just do:


SELECT * FROM sampleTable;

Then put the results in an array, and filter the array out to only contain values you want. Much easier, and cleaner, than running an additional SQL query.

 

[ForumMaster]

from a google search, it looks like ms access doesn't have any system info view about tables that can tell you the columns of a table. therefor, i'd go with manlymatt83's suggestion. that's what suggestion in every thread has been. use an array to get all the columns from select * from whatevertable, then use a simple regex to get the columns you want, and build a new select statement. quite easy.