handling null or empty values useing update statement in sql server

Question

handling null or empty values useing update statement in sql server

asranantha

SSCarpal Tunnel

Points: 4246
More actions
October 19, 2013 at 11:30 am

#299330

Hi friends i have small doubt in sql server how to update 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 useing update query
actualy i tried like below query but its not give above exactly result
update emp set name=(case isnull(name,'NA') when '' then 'NA'else name end),
sal =(case isnull(sal,'NA')when '' then 'NA' else sal end) ,
deptno=(case isnull(desptno,'NA') when '' then 'NA' else deptno end)
from emp.
here its not given exactely output.plese tell me update query in sql server.

Viewing 3 posts - 1 through 2 (of 2 total)

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

jonysuise Ten Centuries Points: 1206 More actions · Answer 1

asranantha (10/19/2013)
Hi friends i have small doubt in sql server how to update 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 useing update query
actualy i tried like below query but its not give above exactly result
update emp set name=(case isnull(name,'NA') when '' then 'NA'else name end),
sal =(case isnull(sal,'NA')when '' then 'NA' else sal end) ,
deptno=(case isnull(desptno,'NA') when '' then 'NA' else deptno end)
from emp.
here its not given exactely output.plese tell me update query in sql server.

Hi asranantha, 😀

I would use this query :

update emp

set name = CASE WHEN coalesce(name,'') = '' THEN 'NA' ELSE name END,

sal = CASE WHEN coalesce(sal,'') = '' THEN 'NA' ELSE sal END,

deptno = CASE WHEN coalesce(deptno,'') = '' THEN 'NA' ELSE deptno END

Hope it helps ! 😎

Jonathan Bernardez Bernardez
___________________________________________________________
DBD. MCSA SQL Server 2012

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

Please don't post multiple threads for the same thing. See the original thread here. http://www.sqlservercentral.com/Forums/Topic1506273-391-1.aspx

_______________________________________________________________

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/