I was querying to find the first non null address value using the COALESCE function.And I got the correct result.

But then I jumped into another question and i.e what if I need to find the column name without knowing the column name

and just by using the column value.

What I mean is this...

My query was.....

SELECT COALESCE(AddressLine1,AddressLine2) AS [Addresss] FROM Person.Address

This is what I got.

Address
#500-75 O'Connor Street
#9900 2700 Production Way
00, rue Saint-Lazare
02, place de Fontenoy
035, boulevard du Montparnasse
081, boulevard du Montparnasse
081, boulevard du Montparnasse
084, boulevard du Montparnasse
1 Corporate Center Drive
1 Mt. Dell Drive

But then what if I just know the address of that person i.e

#500-75 O'Connor Street

How am I suppose to retrieve that without knowing the column name.

I hope you understood my question.

Thanks

You can SELECT by column numbers like 4, 7 as well.

Column names are available in INFORMATION_SCHEMA.COLUMNS system view.  This kind of data is called metadata - data which defines the structure of the database.

How to use the INFORMATION_SCHEMA views?

Hi Smita, 

In this case you can use OR operator on the columns of table to find that person.

for example: 

select * FROM Person.Address
where AddressLine1 = '#500-75 O''Connor Street' OR AddressLine2 = '#500-75 O''Connor Street'