Zero(0) value check in SQL Server

Here we discuss about Zero(0) value check in SQL Server. If any field value is 0 then we put another value instead of Zero(0).

So, first we can do it using a function. we create a function and use it. 

IF EXISTS(SELECT * FROM SysObjects WHERE NAME = 'ISZERO' AND XTYPE = 'FN')

      BEGIN

            DROP FUNCTION ISZERO

      END

GO

      CREATE FUNCTION [dbo].[ISZERO] (@Number FLOAT,
                                      @IsZeroNumber FLOAT)

      RETURNS FLOAT

      AS

      BEGIN

            IF (@Number = 0)

            BEGIN

                  SET @Number = @IsZeroNumber

            END

            RETURN (@Number)

      END

GO

      DECLARE @ZERO AS FLOAT = 0

      DECLARE @NUMBER AS FLOAT = 3103.2016

GO

      SELECT [dbo].[ISZERO](@ZERO,@NUMBER)

RESULT: 3103.2016

And the another way we use ISNULL() and NULLIF().

DECLARE @ZERO AS FLOAT = 0

DECLARE @NUMBER AS FLOAT = 3103.2016

GO

SELECT ISNULL(NULLIF(@ZERO,0),@NUMBER)

RESULT: 3103.2016

COALESCE in SQL Server and Reverse it

  The COALESCE function in SQL returns the first non-NULL expression among its arguments. Here we use this function to different purpose. We use COALESCE function to arrange the column values in a single row using comma (,) separator.

USE tempdb

GO

   CREATE TABLE #TEMP (NAME VARCHAR(MAX))

   INSERT INTO #TEMP

SELECT 'C' UNION ALL SELECT 'C++' UNION ALL

SELECT 'JAVA' UNION ALL SELECT 'SQL SERVER' UNION ALL

SELECT 'VB.NET' UNION ALL SELECT 'PHP' UNION ALL

SELECT 'ASP.NET' UNION ALL SELECT 'SAP B1' UNION ALL SELECT 'SAP HANA'

GO   

   SELECT * FROM #TEMP

GO

GO

DECLARE @String VARCHAR(8000)    

SELECT @String = COALESCE(@String +',','') + NAME FROM   

      (SELECT NAME FROM #TEMP)V1   

SELECT @String 

DROP TABLE #TEMP

Now we see the reverse i.e. Arrange a single row value into the column

DECLARE @String VARCHAR(MAX)

SET @String = 'C,C++,JAVA,SQL SERVER,VB.NET,PHP,ASP.NET,SAP B1,SAP HANA'

DECLARE @XML XML

SET @XML = CAST('' + REPLACE(@String, ',','

') + '' AS XML)

SELECT V2.NAME INTO #TEMP

FROM

      (SELECT V1.NAME

       FROM

         (SELECT T.i.value('.', 'VARCHAR(MAX)')'NAME' FROM @XML.nodes('TAG') T(i)

          )V1

      )V2

GO   

SELECT * FROM #TEMP

GO

DROP TABLE #TEMP

If there is any other better option,

Please share it here or mail to me arkaa4@gmail.com

Thank You,
Arka Gupta.

http://arkaguptablog.blogspot.in/

https://plus.google.com/+ArkaGupta-arkagupta/posts