CoDebate

Debating Code - [ASP.NET, C#, Sql Server, VB.NET]

Wednesday, July 1, 2009

Strange Behavior of NULL Values in SQL Queries

I found a strange behavior of NULL Values when I have a table values as in the following:

Declare @val table
(
Id int,
Username varchar(100),
Desg varchar(100),
Level varchar(100)
)

Insert into @val
select 1,'AAA',NULL,1 union all
select 1,'BBB',NULL,1 union all
select NULL,'CCC','SE',1 union all
select 2,NULL,'PL',4 union all
select 2,'EEE',NULL,5

-- Query 1
select * from @val

-- Query 2
select Id,Username,desg,COUNT(*)
[No. of Employees] from @val
where desg <> 'SE'
group by Id,Username,Desg

-- Query 3
select Id,Username,desg,COUNT(*)
[No. of Employees] from @val
where desg is null or desg <> 'SE'
group by Id,Username,Desg


When we execute the above queries in the Sql Server Management Studio, we would get the following outputs:



Query 1 Result:



Full Table Selection



This is the output of the entire table we just created.



Query 2 Result:



Condition: desg  'SE'



This is the output when I use this condition – desg <> ‘SE’. But it should show the remaining rows that are with NULL values.



Query 3 Result:



Condition: desg IS NULL OR desg  'SE'



when I use this condition – desg IS NULL or desg <> ‘SE’, I am able to get the required output.



Somebody who knows can explain the trick ;-)



Labels: , , , , ,

posted by Karthikeyan @ 10:22 AM 1 Comments

ASP.NET EMail Validation – Revised.

Sometimes back, I had searched to find an easy way to validate email address with some restricted patterns such as fixed domains, common suffices, etc.

The very common regular expression for email validation is as we know:

\w+([-+.']\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*

But if we want to restrict emails of specific domains, that is also simply possible by :

\w+([-+.']\w+)*@(domA\.com|domB\.com)

The above expression will validate only if the emails are with @domA.com and @domB.com in the suffix.

And When we want to have a specific pattern in the email:


\w+([-+.']\w+)*@(domA+[A-Za-z0-9]*.com)

With the above expression, we can validate email ids with the format abc@domAbbb.com, acde@domAcgfdg32.com, etc. As we could have @domA as the part of the email address after @ symbol.

Labels: , , , , , , ,

posted by Karthikeyan @ 9:28 AM 0 Comments