MSSQL - Extracting Part Of An EMail

24. June 2011 17:10

 

Somethnig that I wanted to do the other day was to parse out the different parts of and email address. The user and the domain part. However the data I was using was quite poor. I also wanted to be able to use this information in a computed column.

 

First of all I tried it this way.

 

User part

 

left([EMailAddress],charindex('@',[EMailAddress])-(1))

 

Domain part

 

right([EMailAddress],len([EMailAddress])-charindex('@',[EMailAddress]))

 

Which of course would crash on poor data. So they very quickly turned into functions so they were easyier to change and also decided that they should return null if there was no '@' in the data.

 

 

CREATE FUNCTION EMailExtractDomain
      (@EMail VARCHAR(MAX))
RETURNS VARCHAR(MAX)
BEGIN
      DECLARE @idx INT
      DECLARE @domain VARCHAR(MAX)

      SELECT @idx = CHARINDEX('@', @EMail)
      IF @idx = 0
            RETURN NULL

      SELECT @domain = SUBSTRING(@EMail, @idx + 1, LEN(@EMail))
      RETURN @domain
END
GO

CREATE FUNCTION EMailExtractUser
      (@EMail VARCHAR(MAX))
RETURNS VARCHAR(MAX)
BEGIN
      DECLARE @idx INT
      DECLARE @domain VARCHAR(MAX)

      SELECT @idx = CHARINDEX('@', @EMail)
      IF @idx = 0
            RETURN NULL

      SELECT @domain = SUBSTRING(@EMail, 0, @idx)
      RETURN @domain
END
GO

 

 

I also ran them though a set of quick tests.

 

SELECT dbo.EMailExtractUser('nobody@example.com'), dbo.EMailExtractDomain('nobody@example.com')
SELECT dbo.EMailExtractUser('example.com'), dbo.EMailExtractDomain('example.com')
SELECT dbo.EMailExtractUser(''), dbo.EMailExtractDomain('')
SELECT dbo.EMailExtractUser(NULL), dbo.EMailExtractDomain(NULL)

 

Enjoy Smile

E-mail Kick it! DZone it! del.icio.us Permalink


C# - Extending any class or type

22. June 2011 18:23

 

Many people are not aware of this. It is possible since .NET 3.5 to extend any class in c# to make it appear that extra functions have been added to a class. Here is a short example of being able to add an extra function to the bool class / type that exists in c#

 

 

public static class ExtenderBool
{
    public static string ToHuman(this bool b)
    {
        if (b)
            return "Yes";
        return "No";
    }

}

 

 

You can then use this in c# so long as the namespace is in scope from the calling function. Here is a quick example of a program to test it.

 

 

class Program
    {
        static void Main(string[] args)
        {
            bool tmp = true;

            Console.WriteLine("Value: {0}", tmp.ToHuman());
            Console.ReadLine();
        }
    }

 

 

The above works because the compiler will figure out that you want it to be able to use the function with that class as a argument to the static function though It does not actually add it to the class it self.

E-mail Kick it! DZone it! del.icio.us Permalink


Cisco Gateway Load Balancing

21. June 2011 18:00

 

What is GLBP?

 

GLBP is a protocol for load balancing routers but make them appear to be a single router to other devices on a network. GLBP stands for gateway load balancing protocol. An example of a situation where this could be used is for balancing between two broadband connections for a small to medium office. As far I as am aware it will only work with cisco routers and nobody else support the GLBP protocol. A full description can be found on the cisco web site for the command reference.

 

How does it work?

 

It works the same way as other redundant gateway protocols work. Like HSRP or VRRP. However in GLBP instead of one or other router being the active gateway both routers share an ip address to make them both active in the setup. So as an added benefit of load balancing you can also have the added benefit of redundancy for free. It is possible for the device to share a single ip address because it will only ever advertise one mac / arp address to each host that requests the gateway.

 

Where is the benefit?

 

What it will not be able to do is to double an internet connection bandwidth to a single machine when nat is involved. What it will do is spread the load from multiple machines into two or more internet connections. So it is best suited to growing offices where there are too many people for a single internet connection but not yet enough to consider getting a costly leased line.

More...

E-mail Kick it! DZone it! del.icio.us Permalink


WTF - Build Scripts. Recursive? Force?

17. June 2011 18:00

 

Sometimes devlopers just don't get it right! Here is a great example on an open source project where they have managed to get a script really really wrong.

 

The original problem is here and the problem is a simple typo in the install / upgrade scripts for the package. Where a space has been inserted into command line which seperated the single argument into the command line into two parts.

 

The command was really meant to be

 

rm -rf /usr/lib/nvidia-current/xorg/xorg


But was accidently


rm -rf /usr /lib/nvidia-current/xorg/xorg


For the non linux / unix people out there this is the same as removing the "Program Files" folder from the machine when the package was installed.

 

Ouch!

E-mail Kick it! DZone it! del.icio.us Permalink


MSSQL - Bulk Deleting rows with backup

15. June 2011 18:00

Something that is a common problem in mssql server is deleting a very large number of rows from a table. Normally due to locking the table that the very long delete is running on. There is a few common ways of doing this. Mostly by setting row count and looping though a delete until it runs out of rows to delete. In sql server 2008 it is also possible todo this by moving the data from one table to another using a delete and the output clause.

 

SET ROWCOUNT 5000
WHILE (1 = 1)
BEGIN
	BEGIN TRAN
	DELETE FROM T WHERE N > 5000
	IF @@ROWCOUNT = 0
		BREAK
END
SET ROWCOUNT 0

 

Or like this

 

 

SET ROWCOUNT 5000
WHILE EXISTS(SELECT * FROM T WHERE N > 5000)
BEGIN
	DELETE FROM T WHERE N > 5000
END
SET ROWCOUNT 0

 

But what if we also wanted to take a backup of the data at the same time as we delete it. Just incase we have made any mistakes. Well this is possible using the OUTPUT DELETED clause in mssql server using the following code. Of course you will need to create the table we are going to backup to just prior to running this. In this example we are using T as the main table and TBackup as our backup table that we are moving data to.

 

 

SET ROWCOUNT 5000
WHILE EXISTS(SELECT * FROM T WHERE N > 5000)
BEGIN
	INSERT INTO TBackup
		SELECT * FROM (
			DELETE FROM T
				OUTPUT DELETED.*
				WHERE N > 5000
		) a
END
SET ROWCOUNT 0

 

 

So now we have all the data we wanted out of the main table into a second table. This may be purged later or we are simply moving things into an archive table.

E-mail Kick it! DZone it! del.icio.us Permalink