get the position of a value in a column

Question

How do I get the position of a given value inside a table column. I need to get the column number.

Answer 1

In psuedo-code:

• For each through the column column collection in the result set.
• When you find the value, note the index number

This assumes one row only.

You can't do this in T-SQL: only a client language such as .net or Java

Answer 2

One option is to query the ColID column from syscolumns for your table [ select [name],[colid] from dbo.syscolumns where [id] = object_id('tablename') ]. Note that I'm not sure if this is guaranteed to be sequential or if gaps could appear if a column is dropped.