Return Null If A Value Is A Certain Value

Sometimes you may have to return a Null value if  the value of your data is a certain value. Here
i have given some ways to do this... check it out..

 
NULLIF
DECLARE @1 char(1)
SELECT @1 ='D'

SELECT NULLIF(@1,'D')


REPLACE
This should not really be used, I just added it here to demonstrate that you can in fact use it.

DECLARE @1 char(1)
SELECT @1 ='D'

SELECT REPLACE(@1,'D',NULL)


CASE
With case you can test for a range of values. You can test for example for values between A and D. If you reverse the logic then you also don't need to provide the ELSE part since it defaults to NULL anyway.

DECLARE @1 char(1)
SELECT @1 ='D'


SELECT CASE @1 WHEN 'D' THEN NULL ELSE @1 END

--No else needed
SELECT CASE WHEN @1 <> 'D' THEN @1 END

And this is how you test for a range.

--Null
DECLARE @1 char(1)
SELECT @1 ='D'

SELECT CASE WHEN @1 BETWEEN 'A' AND 'D' THEN NULL ELSE @1 END

--E
DECLARE @1 char(1)
SELECT @1 ='E'

SELECT CASE WHEN @1 BETWEEN 'A' AND 'D' THEN NULL ELSE @1 END

Retrieve TOP and BOTTOM Rows using SQL Query

Here my table name is MT_Sample_Info


SELECT *
FROM MT_Sample_Info
WHERE sid IN (
SELECT TOP 1 MIN(sid) sid
FROM MT_Sample_Info
UNION ALL
SELECT TOP 1 MAX(sid) sid
FROM MT_Sample_Info)

SQL Query to find first day of month?

This is an interview question i have faced in most of the companies....

it's looks easy but it will trouble you at the interview hall........


SELECT DATENAME(dw, DATEADD(dd, - DATEPART(dd, GETDATE()) + 1, GETDATE())) AS FirstDay

SQL Query to export data from sql server to excel

– Create one Excel File in c:\Test.xls and rename the Sheet1 to Emp. The Sheet should contain 2 columns EmployeeID,Title

USE [Production]

INSERT INTOOPENROWSET(‘Microsoft.Jet.OLEDB.4.0′,‘Excel8.0;Database=c:\Test.xls;’,‘Select * from [Emp$]’)

Select EmployeeID, Title FROM Employment.Employee

Creating a Delimited List in SQL Server without Cursors or Looping Using Coalesce or IsNull

Suppose you wanted to return a list of names from a Customer table in a database in a comma delimited list. You could create a SQL Query and loop through the rows with a cursor or you could return the result set and loop through it your application code, but there is another alternative. You can use this clever bit of SQL code:

declare @MyList varchar(1000)


SELECT @MyList = Coalesce(@MyList + ', ', '') + field_name

from table_name

print @MyList
You can also substitute IsNull for Coalesce when I first heard about this technique Coalesce was used, but IsNull seems to work just as well. The reason it works is because as the select takes place it appends the field value to the variable for each row in the result set. The IsNull or Coalesce will keep it from putting a delimiter in front of the first item