Selecting column by its number

Question

How to select table's columns using its number? For example:

Select col:1, col:2
From Banks

instead of

Select Id, Name
From Banks

I have problem like this - I have list of selecting queries in database, but first and second columns has different names/aliases:

Id, Name
Name, Description
1 CODE, 'Male' VALUE
...

I want to filter them with a parameter:

Select Id, Name
From Banks
Where lower(Name) like lower(''%' + p_bank_name + '%'')'

So, how can I write this code:

Select col:1, col:2
From Banks
Where lower(col:2) like lower(''%' + p_bank_name + '%'')'

??

Also, I can write sub queries:

Select col:1, col:2
From (
  Select Id, Name
  From Banks
) r
Where lower(col:2) like lower(''%' + p_bank_name + '%'')'

But, how??

Why tag `tsql` and `plsql`; that completely different dialects of SQL. I have remvoed these conflicting tags and added the generic `sql` tag. Please retag the SQL dialect **and** RDBMS you are using. I am only not sure what you're using here. The SQL you *have* provided is full of typographical errors, you have syntax like `Select col:1` (which isn't valid T-SQL), but *appear* to use `+` as the concatenation operator (and PL\SQL uses `||`). — Thom A, Oct 23 '19 at 13:15
To answer your question *"How to select table's columns using its number"* for T-SQL, you can't, without dynamic SQL, but **don't**. The ordinal position of a column in a table is utterly meaningless. — Thom A, Oct 23 '19 at 13:19
You can only do this by constructing a dynamic query using the system information tables. — Gordon Linoff, Oct 23 '19 at 13:20
Possible duplicate of [sql server select column by number](https://stackoverflow.com/questions/17885667/sql-server-select-column-by-number) or https://stackoverflow.com/questions/368505/is-it-possible-to-select-sql-server-data-using-column-ordinal-position — Sebastian Brosch, Oct 23 '19 at 13:22
I've added both, tsql and plsql, cause I want the answer in a both language. I wrote col:1, cause I want to replace it with something else, that is right function/structure/.. it was only demonstration, not a code. Dynamically, I can do it, yes, but I want to do this in one select - as you are telling me, it is not impossible, so - OK. These duplicates has less demonstrations, than mine, I've added selections, but if my query is impossible, I'm going to write aliases to the queries on the columns in my db.. Thanks a lot — GuRAm, Oct 23 '19 at 13:53

N Subedi · Accepted Answer · 2019-10-23T14:00:06.653

This is not exactly same as what you are trying to do. However, It is almost there. It won't select column by number, however you dont have to specify the explicit column from your real table while writing this query.

As all us suggested, you have to use the dynamic SQL. This is a little idea I created:

create table test1(name1 varchar(10), address1 varchar(10), zipcode1 varchar(10))

insert into test1 values('Test1.1','USA','12344')
insert into test1 values('Test1.2','USA','12344')
insert into test1 values('Test1.3','USA','12344')
insert into test1 values('Test1.4','USA','12344')

create table test2(name2 varchar(10), address2 varchar(10), zipcode2 varchar(10))

insert into test2 values('Test2.1','USA','12344')
insert into test2 values('Test2.2','USA','12344')
insert into test2 values('Test2.3','USA','12344')
insert into test2 values('Test2.4','USA','12344')

You see, the Table name, and the Column name are completely different in both. Now this sql statement doesn't care about column names :

select * from
(
    select '' as T1, '' as T2, '' as T3
    union all
    select * from test1   --No matter whether it is Id, Name or description
    union all
    select * from test2   --No matter whether it is Id, Name or description
) as D
where D.T1<>''  -- your other conditions!

Only issue is, since we are using Union, you have to match the number of columns when you specify your empty columns:

select '' as T1, '' as T2, '' as T3, '' as T4, 0 as T5 -- and so on

Here's the output:

This answer gives me right solution.. But, I have one error in data converting - different type columns (I have number-string, or string-string, or number-number,.. but not date). For now I want to use function like to_char(*) for both of columns (in plsql..). — GuRAm, Oct 23 '19 at 14:11
You have to maintain the ordering of the empty columns with the data types. If it becomes complex you have to use dynamic sql. — N Subedi, Oct 23 '19 at 14:15
I've added null values instead of '' and I have solved mo problem.. — GuRAm, Oct 23 '19 at 14:27

Selecting column by its number

1 Answers1