SQL tips and articles

Recently I found 2 useful SQL functions and would like to share with you.

1.       Parsename http://msdn.microsoft.com/en-us/library/ms188006.aspx

This function is quite useful for a DBA.

For example:

DECLARE @Object varchar(100)

SELECT @Object = 'Mem.dbo.Member'

SELECT

      PARSENAME (@Object ,3),

      PARSENAME (@Object ,2), 

      PARSENAME (@Object ,1)

You could also apply the same concept to e.g. split IP format

For example: instead of using complex charindex

DECLARE @IP char(15)

SELECT @IP = '192.168.3.11'

SELECT

      PARSENAME (@IP ,4),

      PARSENAME (@IP ,3), 

      PARSENAME (@IP ,2),

      PARSENAME (@IP ,1)   

2.       Coalesce  http://msdn.microsoft.com/en-us/library/ms190349.aspx

http://www.mssqltips.com/sqlservertip/1521/the-many-uses-of-coalesce-in-sql-server/?utm_source=dailynewsletter&utm_medium=email&utm_content=headline&utm_campaign=201294

What it does is returning the first non-null argument. It is equivalent to CASE

CASE

WHEN (expression1 IS NOT NULL) THEN expression1

WHEN (expression2 IS NOT NULL) THEN expression2

...

ELSE expression

END