January | 2013 | SQL Server Portal

Archive for January, 2013

SQL SERVER – Get Number of Days in a Month

Posted in Solutions, SQL SERVER, tagged raresql, SQL, SQL Server, SQL SERVER – Get Number of Days in Month on January 6, 2013| 1 Comment »

In my previous article, I discussed about the easiest way to calculate the last date of any month in SQL SERVER 2012.
Today, we will discuss the easiest way to calculate the total number of days in a month in SQL SERVER 2012.
But before that, I would like to discuss how we were calculating the total number of days in a month in the previous versions of SQL SERVER (2005 & 2008)

In SQL Server 2005/2008

For Example :

DECLARE @DATE DATETIME
SET @DATE='2012-12-10'

SELECT DAY(DATEADD(ms,-2,DATEADD(MONTH, DATEDIFF(MONTH,0,@DATE)+1,0)))
AS [Current Month]

--RESULT
Current Month
-------------
29

(1 row(s) affected)

In SQL Server 2012

For Example :

DECLARE @DATE DATETIME
SET @DATE='2012-02-10'

Select Day(EOMONTH(@DATE)) AS [Current Month]
--RESULT
Current Month
-------------
29

(1 row(s) affected)

Read Full Post »

SQL SERVER – User Defined Function – Parse HTML

Posted in Functions, SQL SERVER, tagged raresql, SQL, SQL Server, SQL SERVER – User Defined Function – Parse HTML, Strip HTML on January 3, 2013| Leave a Comment »

Today, I came across a situation where I needed to parse HTML tags and get plain text from it, and we do not have a built-in function in SQL SERVER to do it. So, I searched the solution over the internet but most of the solutions are designed from the loop.
So, I thought of doing it without loop and with the help of XQuery.
Given below is the user defined function to remove all HTML tags (“< >”) from any HTML string and return plain text.

CREATE FUNCTION dbo.[UDF_Parse_HTML_From_String]
(
@HTML_STRING VARCHAR(MAX) -- Variable for string
)
RETURNS VARCHAR(MAX)
BEGIN

DECLARE @STRING VARCHAR(MAX)
Declare @Xml AS XML
SET @Xml = CAST(('<A>'+ REPLACE(REPLACE(REPLACE(REPLACE(@HTML_STRING
,'<','@*'),'>','!'),'@','</A><A>'),'!','</A><A>') +'</A>') AS XML)

;WITH CTE AS (SELECT A.value('.', 'VARCHAR(MAX)') [A]
FROM @Xml.nodes('A') AS FN(A) WHERE CHARINDEX('*',
A.value('.', 'VARCHAR(MAX)'))=0
AND ISNULL(A.value('.', 'varchar(max)'),'')<>'')

SELECT @STRING=STUFF((SELECT ' ' + [A] FROM CTE FOR XML PATH('')),1,1,'')
RETURN @STRING
END
GO
SELECT dbo.[UDF_Parse_HTML_From_String] ('<b>This is raresql.com</b><h2>HTML Parse User Defined Function</h2><a href="http://raresql.com"></a>') as [Text]
--OUTPUT