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

Removing a constraint from a table

Here i have given the syntax and query for removing a constraint from a table...

syntax:
------
ALTER TABLE (tbl name) DROP CONSTRAINT (CONSTRAINT name)


Eg:
--
ALTER TABLE Proddet_Details DROP CONSTRAINT  FK_PCIT_Details

How to copy structure of table

syntax:
---------
Select * Into (DestinationTableName) From (SourceTableName) Where 1 = 2

Example:
-----------
Select * Into my_new_table From emp Where 1=2
 

Friends ....this will just create the table structure as the source table

Wont create any constraint or triggers on the table..

and if you also want to copy the data then remove the where clause..  

Friends post  your comments or questions  on my blog to improve my posts .... 

How to use IN operator ?

Sometimes you want to test a given value against a list of values. Here you can use the special comparison operator IN to get it done. Below i have given two examples using IN operator..

Example 1:

DECLARE @my_id INT;
SET @my_id = 767;
SELECT CASE WHEN
@my_id IN (525, 272, 532, 767, 150, 637)
THEN 'I m Kavya.'
ELSE 'I m not Kavya.'
END;
GO

o/p: I m Kavya


Example 2:

DECLARE @my_id INT;
SET @my_id = 676;
SELECT CASE WHEN
@my_id NOT IN (525, 272, 532, 767, 150, 637)
THEN 'I m not Kavya.'
ELSE 'I m Kavya.'
END;
GO

o/p: I m not Kavya

How To Test Subquery Results with the EXISTS Operator?

EXISTS is a special operator used to test subquery results. EXISTS can be used in two ways:

EXISTS (SELECT ...)
-- Returns TRUE if the specified subquery has one or more rows returned.

NOT EXISTS (SELECT ...)
-- Returns TRUE if the specified subquery no rows returned.

Example:

-- Number of customers with orders
SELECT COUNT(*) FROM SalesLT.Customer c WHERE
EXISTS
(SELECT * FROM SalesLT.SalesOrderHeader s WHERE s.CustomerID = c.CustomerID)


-- Number of customers without orders
SELECT COUNT(*) FROM SalesLT.Customer c WHERE
NOT EXISTS
(SELECT * FROM SalesLT.SalesOrderHeader s WHERE s.CustomerID = c.CustomerID)

Pattern matching - One or more occurances of a character

Here is the issue.

I have a table like this..

create table abcCollection(abc varchar(30) not null)

And I insert the following data

INSERT abcCollection values('ABC')
INSERT abcCollection values('ABxC')
INSERT abcCollection values('ABBBC')
INSERT abcCollection values('ABBBBxBC')
INSERT abcCollection values('ABxBBBBBBBC')
INSERT abcCollection values('123ABBBC456AB')
INSERT abcCollection values('dfdfddsds')
INSERT abcCollection values('acdaccdbbvfcab')
INSERT abcCollection values('acbcabbbfc')
INSERT abcCollection values('acbcabbbfc')
INSERT abcCollection values('abbbcafffc')
INSERT abcCollection values('acabc')
INSERT abcCollection values('acac')
INSERT abcCollection values ('abbdddbbc')
INSERT abcCollection values ('abbdddbabc')

Now I want to get the following result

ABC
ABBBC
123ABBBC456AB
abbbcafffc
acabc
abbdddbabc

That is, the result should contain A followed by one or more Bs followed by C. This pattern can appear anywhere within the string.

We cannot directly use the LIKE operator since it doesn't support this kind of a search.

There are quite a few methods to solve this. I am including all the solutions here.

The following methods will work for SQL Server 2000 and 2005.

Method 1: Call a recursive function.
This function keeps replacing 2 continous Bs with a single B till it doesn't find any and then search for 'ABC'

create function dbo.fn1(@input varchar(30))
returns bit
as
begin
return case when @input like '%abc%' then 1
when charindex('bb',@input) > 0 then dbo.fn1(replace(@input,'bb','b'))
else 0 end
end

And you can call the function like this..

select abc from abcCollection where dbo.fn1(abc) = 1

But, This is based on recursion and you can nest it only 32 times. So if your text has more than 32 Bs continously, then it might fail and its performance intensive too.

Method 2: Use a while loop within the function.
The logic of this function is similar to Method 1 with a different implementation.

create function fn1 (@str as varchar(30)) returns varchar(30)
as
begin
while (PATINDEX('%BB%', @str) > 0)
begin
set @str = replace (@str,'BB','B')
end
return @str
end

And you can call the function this way:

select abc from abcCollection where PATINDEX ('%ABC%', dbo.fn1(abc)) > 0

While this will work for big strings, you will have to execute a while loop for every column and so its performance intensive.

Method 3: Filter in the query (smart replace)
This solution is an absolute brainer, originally conceived by Robert Carnegie a long time back, shown to me by Steve Kass:

select abc from abcCollection where
replace(replace(replace(abc,'B','<>'),'><',''),'<>','B')
like '%ABC%'

Here is how it works:
Take for example you have the string 'ABBBC'
First replace will replace 'B' with '<>'
So the string becomes A<><><>C
The Second replace will replace '><' with an empty string( '' ) So A<><><>C becomes A<>C
Now the third replace replaces '<>' with 'B'
So it becomes ABC and now this is compared against ABC.

Method 4:
Another version conceived from Method 3

select abc from abcCollection where replace(replace(abc,'ac',''),'b','') like '%ac%'

Here is what it does:
Take a string like this 'ACDFABBBCCA'
When you replace 'AC' with empty string ( '' ) it becomes 'DFABBBCCA'
Now replace 'B' with an empty string, it becomes 'DFACCA'
Now this string is compared against 'AC'

The following is the implementation of Method 1 in SQL Server 2005 using common table expressions. Its still a bad performer when compared to Methods 3 and 4. But here it is anyways :)


with cte as
(select abc,replace(abc,'bb','b') as d from abcCollection
union all
select abc, replace(d,'bb','b') from cte where d <> replace(d,'bb','b')
)
select abc from cte where d like '%abc%'