how to handled Nulls and empty values in sql server

Question

how to handled Nulls and empty values in sql server

asranantha

SSCarpal Tunnel

Points: 4246
More actions
October 18, 2013 at 11:05 am

#299328

Hi friends i have small doubt in sql server how to handled nulls and empty values and replac other values in sql server
table data look like below
table :emp
id ,name ,sal ,deptno
1 ,abc ,1oo ,10
2 ,venu ,2000 ,null
3 ,null , ,20
4 ,balu ,null ,
5 ,hari , ,30
based on above table i want output like below
id , name ,sal ,deptno
1 , abc ,100 ,10
2 , venu ,2000 ,NA
3 , NA , NA ,20
4 , balu ,NA ,NA
5 , hari ,NA ,30
plese tell me how to write query while achive in above issuse.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

asranantha (10/18/2013)
Hi friends i have small doubt in sql server how to handled nulls and empty values and replac other values in sql server
table data look like below
table :emp
id ,name ,sal ,deptno
1 ,abc ,1oo ,10
2 ,venu ,2000 ,null
3 ,null , ,20
4 ,balu ,null ,
5 ,hari , ,30
based on above table i want output like below
id , name ,sal ,deptno
1 , abc ,100 ,10
2 , venu ,2000 ,NA
3 , NA , NA ,20
4 , balu ,NA ,NA
5 , hari ,NA ,30
plese tell me how to write query while achive in above issuse.

This is a little odd given the data you presented. Here are a couple of ways you do it.

select isnull(nullif('', YourColumn), 'NA') as YourColumn,

case when YourColumn IS NULL OR YourColumn = '' then 'NA' else YourColumn end as YourColumn

Here is the problem. The data you posted appears to be ints. You can't mix int and varchar data in the same column unless you cast your numeric data to a varchar. If at all possible I would do this type of formatting in the front end instead of in sql.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

jonysuise Ten Centuries Points: 1206 More actions · Answer 2

Hi asranantha, 😀

I would use COALESCE AND CASE, because it's SQL standard.

SELECT CASE COALESCE(your_column,'NA') WHEN '' THEN 'NA' ELSE your_column END

Hope it helps. 😎

Jonathan Bernardez Bernardez
___________________________________________________________
DBD. MCSA SQL Server 2012

Jeff Moden SSC Guru Points: 1004313 More actions · Answer 3

jonysuise (10/18/2013)
Hi asranantha, 😀
I would use COALESCE AND CASE, because it's SQL standard.

I would use just ISNULL AND NULLIF (like Sean did) because you don't have unexpected datatype precedence problems with ISNULL like you can with COALESCE, is a tiny bit faster than COALESCE, takes fewer characters to type, and it ticks all sorts of people off that still think SQL is portable across engines. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jonysuise Ten Centuries Points: 1206 More actions · Answer 4

Jeff Moden (10/18/2013)
jonysuise (10/18/2013)
Hi asranantha, 😀
I would use COALESCE AND CASE, because it's SQL standard.
I would use just ISNULL AND NULLIF (like Sean did) because you don't have unexpected datatype precedence problems with ISNULL like you can with COALESCE, is a tiny bit faster than COALESCE, takes fewer characters to type, and it ticks all sorts of people off that still think SQL is portable across engines. 😀

Thanks Jeff. It's a matter of opinion i think. 😀

Jonathan Bernardez Bernardez
___________________________________________________________
DBD. MCSA SQL Server 2012

UmaShankar Patel Ten Centuries Points: 1142 More actions · Answer 5

Check this post

handling-null-values-in-sql-server

Thanks.......
-----------------------------------
My Blog[/url] | Articles

Jeff Moden SSC Guru Points: 1004313 More actions · Answer 6

jonysuise (10/19/2013)
Jeff Moden (10/18/2013)
jonysuise (10/18/2013)
Hi asranantha, 😀
I would use COALESCE AND CASE, because it's SQL standard.
I would use just ISNULL AND NULLIF (like Sean did) because you don't have unexpected datatype precedence problems with ISNULL like you can with COALESCE, is a tiny bit faster than COALESCE, takes fewer characters to type, and it ticks all sorts of people off that still think SQL is portable across engines. 😀
Thanks Jeff. It's a matter of opinion i think. 😀

Actually, if you make a nice little million row table, you can prove to yourself the slight performance gain. You can also prove to yourself that, if you don't mind you're P's and Q's, the data precedence thing can really mess up performance. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jonysuise Ten Centuries Points: 1206 More actions · Answer 7

Jeff Moden (10/19/2013)
jonysuise (10/19/2013)
Jeff Moden (10/18/2013)
jonysuise (10/18/2013)
Hi asranantha, 😀
I would use COALESCE AND CASE, because it's SQL standard.
I would use just ISNULL AND NULLIF (like Sean did) because you don't have unexpected datatype precedence problems with ISNULL like you can with COALESCE, is a tiny bit faster than COALESCE, takes fewer characters to type, and it ticks all sorts of people off that still think SQL is portable across engines. 😀
Thanks Jeff. It's a matter of opinion i think. 😀
Actually, if you make a nice little million row table, you can prove to yourself the slight performance gain. You can also prove to yourself that, if you don't mind you're P's and Q's, the data precedence thing can really mess up performance. 😉

Check this post :

http://sqlmag.com/t-sql/coalesce-vs-isnull

As i said, it's a matter of opinion. I don't think it's correct to point a solution as better than the other one.. just be aware of the pros and cons of each one of them and choose the one that fit your needs. 🙂

Jonathan Bernardez Bernardez
___________________________________________________________
DBD. MCSA SQL Server 2012