NULL if column doesn't exists in SQL

Question

4.40/5 (3 votes)

See more:

I just like to know if it is possible to set a column to null if column doesn't exists.

I know that I can do something like:

SQL

Select
Column1,
NULL as Column2
FROM Table

But what I want to do is NULL the column only if column doesn't exists.

Posted 21-Apr-14 18:45pm

krisedison

Add a Solution

Comments

King Fisher 22-Apr-14 1:06am

yes ,you can..

3 solutions

Solution 2

Your query will be......

SQL

Select Column1, ISNULL(Column2,NULL) AS Column2 FROM Table

Posted 21-Apr-14 18:59pm

Kabi Pr. Sahoo

Comments

VICK 22-Apr-14 6:33am

ISNULL(Column2,NULL)

will check for the null value in Column2 and not if Column2 doest not exist. so off the topic. :(

Solution 1

Hi,

Use ISNULL function available in SQL.

SQL

Select
Column1,
ISNULL(Column2,NULL) AS 'Column2'
FROM Table

Posted 21-Apr-14 18:53pm

Bh@gyesh

Comments

krisedison 22-Apr-14 1:00am

It still says
Invalid column name 'Column2'
BTW, I am on SQL Server 2012

Bh@gyesh 22-Apr-14 1:31am

ok, For that you need to check for every column exists of not in table.

SELECT CASE WHEN exists (SELECT * FROM sys.columns
WHERE [name] = N'column1' AND [object_id] = OBJECT_ID(N'table') THEN Column1 ELSE NULL END) AS 'Column1',
CASE WHEN exists (SELECT * FROM sys.columns
WHERE [name] = N'column2' AND [object_id] = OBJECT_ID(N'table') THEN Column2 ELSE NULL END) AS 'Column2'

FROM TableName

VICK 22-Apr-14 6:34am

SNULL(Column2,NULL)

will check for the null value in Column2 and not if Column2 doest not exist. so off the topic. :(

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Ankur^\m/ · Accepted Answer · 2014-04-21T19:19:00

Solution 3

You can do something like this:
SQL

SELECT Column1, CASE WHEN exists (select null from information_schema.columns where column_name='Column2'and table_name='TableName') THEN Column2 ELSE NULL END AS Column2 FROM TableName

EDIT:
The above query won't compile as the column name do not exist. You will need dynamic SQL to execute such queries. Below is a very basic syntax how you would do it.

SQL

DECLARE @query nvarchar(max) = '' + 'SELECT ' +
        (CASE WHEN EXISTS (SELECT NULL FROM information_schema.columns WHERE column_name='Column2' AND table_name='Table2')
            THEN 'Column2'
            ELSE 'NULL AS Column2' END) +
        ' FROM Table2'
--PRINT @query
EXEC sp_executesql @query

Posted 21-Apr-14 19:19pm

Ankur^\m/

Updated 21-Apr-14 22:59pm

v2

Comments

krisedison 22-Apr-14 2:02am

It still says
Invalid column name 'Column2'
on line with THEN Column2

Ankur^\m/ 22-Apr-14 2:04am

I tested it, it works. Please check you have specified correct table name.

krisedison 22-Apr-14 2:09am

Yes it works if Column2 exists but if not it doesn't. I am now looking to use try and catch.

Ankur^\m/ 22-Apr-14 2:13am

It doesn't require try catch block. The query I have given checks if a column exists in a table, return that else return null. Check the below query and tell me what it returns
select null from information_schema.columns where column_name='Column2'and table_name='TableName'.
Replace Column2 and TableName with correct values. It will either return null or nothing.
It seems you may need to add schema [dbo.] to your table name.

krisedison 22-Apr-14 2:20am

It says Invalid column name.
I didn't changed column_name because I am trying to look for Column2 in the table but I changed table_name value to the table that it will look for. But still, I have the invalid column error.

Ankur^\m/ 22-Apr-14 3:26am

The query I asked you to execute in the above comment cannot give invalid column error. It either returns null or nothing. You are doing something wrong. Add your database schema to the table name. By default it is dbo. So instead of just writing tablename, write dbo.tableName

krisedison 22-Apr-14 3:34am

The query
select null from information_schema.columns where column_name='Column2'and table_name='TableName'
returns NULL or nothing
BUT the query
SELECT Column1,
CASE WHEN exists (select null from information_schema.columns where column_name='Column2'and table_name='TableName') THEN Column2
ELSE NULL END AS Column2
FROM TableName
didn't work if Column2 doesn't exists. I don't know why....

Ankur^\m/ 22-Apr-14 3:57am

Can you give me the exact query that you ran... with the table name (and just 2 columns - one that exists and one that doesn't)?

krisedison 22-Apr-14 4:10am

Please check this http://pastebin.com/sJ6dknEd

Ankur^\m/ 22-Apr-14 4:52am

Okay I see. The query won't compile because the column name do not exist. In such cases, dynamic query works very well. Give me sometime I will update my answer after writing and testing your query.

krisedison 22-Apr-14 5:30am

I see. Thanks for this one. It now works. I am thinking of a way to put this in a function. BTW, thanks a lot!

Ankur^\m/ 22-Apr-14 5:51am

Glad I could help! :)

Ankur^\m/ 22-Apr-14 5:05am

Just checked the web if there is something similar. The below link talks about your issue. And the first solution is exactly same as what I said. There is another one there. Please check - http://stackoverflow.com/questions/16952442/select-columnvalue-if-the-column-exists-otherwise-null