how to handled Nulls and empty values in sql server

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

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

  • 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

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

  • 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

  • Check this post

    handling-null-values-in-sql-server

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

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

  • 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

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

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