handling null or empty values useing update statement in sql server

  • 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.

  • 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

  • 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/

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

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