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
Return Null If A Value Is A Certain Value
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