WebDeveloper's Corner

Return Null If A Value Is A Certain Value

2008-03-12T00:10:00.000-07:00

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

2008-03-11T23:12:00.000-07:00

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?

2008-03-11T02:22:00.000-07:00

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

2008-03-11T01:47:00.000-07:00

– 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

2008-03-11T01:46:00.000-07:00

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

2008-03-07T00:12:00.002-08:00

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 find 5th maximum number through a query in sql

2008-03-06T21:49:00.000-08:00

"How do I find Nth maximum value?" is one of the most asked questions in many

  interviews.... Here are some methods.. to solve this..

Use Inner Join
------------------
  select t1.num from number t1 inner join number t2 on t1.num<=t2.num  group by    t1.num having count(t1.num)=5

Use Top Operator
---------------------
Eg1:
Select top 1 num from(Select top 5 num from number order by num desc) T
order by num asc

Eg2:
Select TOP 1 quantity from findmax where quantity NOT IN (Select TOP 4 quantity FROM findmax order by quantity ASC)
order by quantity ASC

How to copy structure of table

2008-03-06T21:35:00.000-08:00

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

Javascript : Disable Right Click

2008-02-26T02:04:00.000-08:00
You can disable right click on ur webpages to prevent others stealing ur images and other things... place this code above head tag..

//script language="JavaScript"

var message="Sorry, that function is disabled.";

function click(e) {
if (document.all) {
if (event.button == 2) {
alert(message);
return false;
}
}
if (document.layers) {
if (e.which == 3) {
alert(message);
return false;
}
}
}
if (document.layers) {
document.captureEvents(Event.MOUSEDOWN);
}
document.onmousedown=click;

// /script>

How to use IN operator ?

2008-02-23T00:54:00.000-08:00
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?

2008-02-19T05:07:00.000-08:00
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

2008-02-19T00:15:00.000-08:00
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%'