Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

handling null or empty values useing update statement in sql server Expand / Collapse
Author
Message
Posted Saturday, October 19, 2013 11:30 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, August 29, 2014 10:08 PM
Points: 210, Visits: 4,523
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.
Post #1506412
Posted Sunday, October 20, 2013 3:53 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, March 25, 2014 7:22 AM
Points: 77, Visits: 150
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
Post #1506458
Posted Monday, October 21, 2013 7:39 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, August 29, 2014 2:32 PM
Points: 13,302, Visits: 12,168
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1506687
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse