Remove HTML Text from String

Good Morning All,

The other day I had to create a new function within SQL Server.  I think this is pretty poweful and useful so I would like to share what I learned and did.

So what did I have do?  I had to find a way to remove HTML from a string and output just the plain text.  I needed to make sure to output the last whole word and not cut the output in the middle of a word. This function will use the following functions in SQL Server:

• Substring
• Charindex
• Stuff
• Reverse
• Ltrim
• Rtrim

As always let's set up our temp tables with data.  To be able to write this blog entry quicker I used   to populate the HTML strings quicker.  It's a great website to populate your HTML text for you.


--Remove table if its there
IF EXISTS(SELECT * FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..#HtmlText') ) DROP TABLE #HtmlText

--Create new table
CREATE TABLE #HtmlText(ID INT IDENTITY(10000000,1),String TEXT)

--insert values
INSERT INTO #HtmlText(String )
   SELECT '<html>
<head>
<title>HTML Generator Sample Page</title>
</head>
<body>
<h2>
HTML Generator</h2>
<p>
Use this HTML generator to generate HTML code for your own website or blog. Use the toolbar to:</p>
<ul>
<li>
Change the <span style="color:#800000;">text color</span></li>
<li>
Create <strong>bold</strong> or <em>italic</em> text.</li>
<li>
Change the <span style="color:#800000;">text color</span></li>
<li>
Change the <span style="font-family:comic sans ms,cursive;">font family</span> or <span style="font-size:12px;">font size</span></li>
<li>
Create <a href="http://www.html.am/html-codes/links/">hyperlinks</a></li>
<li>
Create a bulleted list...</li>
<li>
...and much more!</li>
</ul>
<p>
To view the source code, click on the &quot;Source&quot; button above (on the left).</p>
</body>
</html>'
   UNION ALL
   SELECT '<!-- Codes by HTML.am -->
<div style="width:150px;height:150px;line-height:3em;overflow:scroll;padding:5px;">
This ''div'' element uses ''overflow:scroll'' to create scrollbars whenever the contents of the ''div'' become too large.
</div>'
   UNION ALL
   SELECT '<!-- Codes by HTML.am -->
<p>Check out <a href="http://www.travel-explorer.com/rob-roy-glacier-in-new-zealand/">Rob Roy Glacier</a>!</p>'   

Now let's jump into the function.  I have two parameters in this function.  The first is @HTMLText and the other is @Textlength.  These pretty much define themselfs.  @HtmlText is the html text you are passing into the function where as the @textLength is the lengh of text you want returned backed.  We need not want the entire string returned so I created this parameter.

Before outputting the entire function I want to review a few important lines.

--Taking the first X amount of chars that include a full word
   SET @HTMLText=  SUBSTRING(@HTMLText,0,@TextLength-(CHARINDEX(' ',REVERSE(SUBSTRING(@HTMLText,0,@TextLength)))))

This line will return the last whole word.  So here is an example.

Whole String: 'Johnny Bench made a wondeful catch on Sunday afternoon.'

Original Output: 'Johnny Bench made a wonderful ca'

Output with Substring: 'Johnny Bench made a wonderful'

As you can see it by be my helpful and meaningful.  

The other import line I created was to remove ' " ' if its the the first char in the string. I talked about using the Stuff command in a previous blog entry.  You can read it here to learn more indepth about it. 

--Removing the " as the beginning of the string if its there.
IF SUBSTRING(@HTMLText,1,1)='"' SET @HTMLText= STUFF(@HTMLText,1,1,'')

Now lets look at the function before doing some simple select statements.

create FUNCTION [dbo].[f_GetPlainTextSubstringFromHTML] (@HTMLText VARCHAR(MAX),@TextLength INT = NULL) RETURNS VARCHAR(MAX)AS

BEGIN

   --variables  
    DECLARE @Start INT
    DECLARE @End INT
    DECLARE @Length INT

    --assigning values to variables
    SET @Start = CHARINDEX('<',@HTMLText)
    SET @End = CHARINDEX('>',@HTMLText,CHARINDEX('<',@HTMLText))
    SET @Length = (@End - @Start) + 1

   IF @TextLength = NULL SET @TextLength = 50

   --Removing HTML Text
   WHILE @Start > 0 AND @End > 0 AND @Length > 0
    BEGIN
        SET @HTMLText = STUFF(@HTMLText,@Start,@Length,'')
        SET @Start = CHARINDEX('<',@HTMLText)
        SET @End = CHARINDEX('>',@HTMLText,CHARINDEX('<',@HTMLText))
        SET @Length = (@End - @Start) + 1
    END
  
   --Taking the first X amoutn of chars that include a full word
   SET @HTMLText=  SUBSTRING(@HTMLText,0,@TextLength-(CHARINDEX(' ',REVERSE(SUBSTRING(@HTMLText,0,@TextLength)))))
  
   --Removing the " as the beginning of the string if its there.
   IF SUBSTRING(@HTMLText,1,1)='"' SET @HTMLText= STUFF(@HTMLText,1,1,'')

   --Just triming the text before outputting it
   RETURN LTRIM(RTRIM(@HTMLText))END

Let's see how this function works.

SELECT String, dbo.f_GetPlainTextSubstringFromHTML(String,100) 
FROM #HtmlText

That should sum up my SQL entry for today.  Have fun and keep learning!

Robert

How to repeat tablix groups on a new page

Good Evening Fellow Readers,

The other day I learned a neat trick in SSRS (Sql Server Reporting Services) that I thought everyone should know.  I need to report group names on each page.  To better understand this I create a before and after screen shot.  Please keep in mind I quickly made this only to demo functionality not design of SSRS.

	Page 1	Page 2
Before
After

As you can see Il, Chicago, and Chicago White Soxes was not being populated on the next page.

Before I show you how I did this let's quickly create some data.

I create a simple store procedure to output a bunch of data.



CREATE PROCEDURE [dbo].[rpt1]
   -- Add the parameters for the stored procedure here AS
BEGIN
   -- SET NOCOUNT ON added to prevent extra result sets from
   -- interfering with SELECT statements.
   SET NOCOUNT ON;
   WITH temp1 (num,PlayerName, Team, City, State)
   AS(
   --Milwaukee Brewers
   SELECT 8,'Ryan Braun','Milwaukee Brewers','Milwaukee','WI'           UNION ALL
   SELECT 27,'Carlos Gomez','Milwaukee Brewers','Milwaukee','WI'        UNION ALL
   SELECT 9,'Jean Segura','Milwaukee Brewers','Milwaukee','WI'          UNION ALL
   SELECT 22,'Matt Garza','Milwaukee Brewers','Milwaukee','WI'          UNION ALL
   SELECT 7,'Mark Reynolds','Milwaukee Brewers','Milwaukee','WI'        UNION ALL
   SELECT 24,'Lyle Overbay','Milwaukee Brewers','Milwaukee','WI'        UNION ALL
   SELECT 23,'Rickie Weeks','Milwaukee Brewers','Milwaukee','WI'        UNION ALL
   SELECT 49,'Yovani Gallardo','Milwaukee Brewers','Milwaukee','WI' UNION ALL
   SELECT 12,'Martin Maldonado','Milwaukee Brewers','Milwaukee','WI'    UNION ALL
   --Atlanta Braves
   SELECT 5,'Freddie FreeMan','Atlanta Braves','Atlanta','GA'           UNION ALL
   SELECT 30,'Ervin Santna','Atlanta Braves','Atlanta','GA'         UNION ALL
   SELECT 24,'Evan Gattis','Atlanta Braves','Atlanta','GA'              UNION ALL
   SELECT 26,'Gan Uggla','Atlanta Braves','Atlanta','GA'                UNION ALL
   SELECT 22,'Jason Heyward','Atlanta Braves','Atlanta','GA'            UNION ALL
   SELECT 2,'B.J. Upton','Atlanta Braves','Atlanta','GA'                UNION ALL
   SELECT 46,'Craig Kimbrel','Atlanta Braves','Atlanta','GA'            UNION ALL
   SELECT 8,'Justin Upton','Atlanta Braves','Atlanta','GA'              UNION ALL
   --Chicago Cubs
   SELECT 29,'Jeff Samardzija','Chicago Cubs','Chicago','Il'            UNION ALL
   SELECT 13,'Starlin Castro','Chicago Cubs','Chicago','Il'         UNION ALL
   SELECT 44,'Anthony Rizzo','Chicago Cubs','Chicago','Il'              UNION ALL
   SELECT 64,'Emilio Bonifacio','Chicago Cubs','Chicago','Il'           UNION ALL
   SELECT 51,'Ryan Kalish','Chicago Cubs','Chicago','Il'                UNION ALL
   SELECT 11,'Kyuji Fujikawa','Chicago Cubs','Chicago','Il'         UNION ALL
   SELECT 15,'Darwin Barney','Chicago Cubs','Chicago','Il'              UNION ALL
   SELECT 68,'Jorge Solar','Chicago Cubs','Chicago','Il'                UNION ALL
   --Chicago White Soxes
   SELECT 79,'Jose Abreu','Chicago White Soxes','Chicago','Il'          UNION ALL
   SELECT 14,'Paul Konerko','Chicago White Soxes','Chicago','Il'        UNION ALL
   SELECT 49,'Chris Sale','Chicago White Soxes','Chicago','Il'          UNION ALL
   SELECT 32,'Adam Dunn','Chicago White Soxes','Chicago','Il'           UNION ALL
   SELECT 26,'Avisail Garcia','Chicago White Soxes','Chicago','Il'      UNION ALL
   SELECT 15,'Gordon Beckham','Chicago White Soxes','Chicago','Il'      UNION ALL
   SELECT 10,'Alexei Ramirez','Chicago White Soxes','Chicago','Il'      UNION ALL
   SELECT 1,'Adam Eaton','Chicago White Soxes','Chicago','Il'           --union all
  
   )SELECT  *  FROM temp1;
END
GO

Now the real fun comes into play.  You need to make sure you have all your group by rows created in SSRS.

First we have to enable advanced mode under column groups.  This was a hidden feature I had no idea was there at first.




After you have enabled advanced mode a few boxes show up under column groups as well as row groups.





Next you need to select each (static) row group and change the setting for 'ReapOnNewPage' to be true.

That is it!  Pretty simple, but it was confusing if you did not know about the advanced mode.


Let's see the Crew tie the series tonight with the Braves!

-Robert

Recursive Sql and Db2

Hello everyone,

The other day I started a new job at a bank.  With a bank comes some pretty old school technology.  I now get the chance to expand my RDBMS knowledge pass SQL Server to DB2.  I created one sweet function the other day and thought I would pass it along.

I need to take a comma list and convert it into the a table using an UDF.  I needed this for a report I was making in SSRS.  In the report there would be a drop down menu with various items to pick.

I found this little blog to explains a cool recursive CTE in DB2.  It did not work 100% right, but I just had to tweak a few lines to make it work for me.

Here is my final version of the SQL:

WITH CTE_Items (ID,StartString,StopString) AS(
   SELECT
       1 AS ID
       ,1 AS StartString
       ,LOCATE('|', '2010-Q1|2011-Q1|2012-Q1|2013-Q1') AS StopString
   FROM SYSIBM.SYSDUMMY1
   WHERE LENGTH('|')>0
     AND LENGTH('2010-Q1|2011-Q1|2012-Q1|2013-Q1')>0

   UNION ALL

   SELECT
       ID + 1
       ,StopString + LENGTH('|')
       ,LOCATE('|', '2010-Q1|2011-Q1|2012-Q1|2013-Q1', StopString + LENGTH('|'))
   FROM
       CTE_Items
   WHERE
       StopString > 0
)
SELECT
     Id,
     substr('2010-Q1|2011-Q1|2012-Q1|2013-Q1',StartString,
                      CASE
                          WHEN StopString=0
                          THEN length('2010-Q1|2011-Q1|2012-Q1|2013-Q1')-startstring+1
                          ELSE StopString-StartString
                      END
                    )
FROM CTE_Items;

To put this into a UDF is pretty simple.  Just create a few variables and there you go...


CREATE FUNCTION [schemaname].UDF_Split(In_Data VARCHAR(32000), In_Delimiter VARCHAR(5) )RETURNS TABLE (ID INT,
                Value VARCHAR(5000)
                )
LANGUAGE SQL
DETERMINISTIC
RETURN
WITH CTE_Items (ID,StartString,StopString) AS(
   SELECT
       1 AS ID
       ,1 AS StartString
       ,LOCATE(In_Delimiter, In_Data) AS StopString
   FROM SYSIBM.SYSDUMMY1
   WHERE LENGTH(In_Delimiter)>0
     AND LENGTH(In_Data)>0

UNION ALL

   SELECT
       ID + 1
       ,StopString + LENGTH(In_Delimiter)
       ,LOCATE(In_Delimiter, In_Data, StopString + LENGTH(In_Delimiter))
   FROM
       CTE_Items
   WHERE
       StopString > 0
)
SELECT ID, 
         substr(In_Data,StartString,
                     CASE 
                           WHEN StopString=0 THEN length(In_Data)-startstring+1 
                           ELSE StopString-StartString
                        END
                )
FROM CTE_Items;COMMIT;

Let's test it out.

Example 1 ('2010-Q1|2011-Q1|2012-Q1|2013-Q1'):

Example 2 ('blue,green,red'):

As you can see you can use any type of delimiter to make the split pretty powerful.  In the store procedure it works perfect.  I query the UDF in the "In" clause of the Sql such as.


SELECT value FROM TABLE("Schemaname"."UDF_SPLIT"'blue,green,red', ',')) AS UDF


One Last issue.  In my SSRS report it was not passing the parameter along towards the store procedure in Db2 correctly.  After doing some googling I found this little blog explaining what you need to do.  Just a small tweak.

You need to go to the following location to change a setting.

Dataset --> Dataset Properties --> Parameters --> Pick parameter's expression --> Put this value in the box.

=join(Parameters!P3.Value,",")


Now the report will run like its suppose to.

Cheers and Go Brewers!

Robert

Recursive T-Sql Query using CTE

Hey Readers!

I learned a cool trick the other day with a recursive query and thought I would pass it on. I wanted a way to find the root ID from a query.

First let's set up some staging tables with data.

IF EXISTS ( SELECT  * FROM tempdb.dbo.sysobjects WHERE   ID = OBJECT_ID(N'tempdb..#Employees') )DROP TABLE #Employees

CREATE TABLE #Employees(
   ID INT,
   ManagerID INT,
   Name VARCHAR(200)
)

INSERT INTO #Employees (ID, ManagerID, Name) VALUES (1,NULL,'Ron Roenicke')
INSERT INTO #Employees (ID, ManagerID, Name) VALUES (2,NULL,'Ryan Braun')
INSERT INTO #Employees (ID, ManagerID, Name) VALUES (3,1,'Prince Fielder')
INSERT INTO #Employees (ID, ManagerID, Name) VALUES (4,2,'Corey Hart')
INSERT INTO #Employees (ID, ManagerID, Name) VALUES (5,2,'Robin Yount')


Now that we have the data inserted. Let's just review the table structure.

SELECT * FROM #Employees



Let's good? I think so, but how do we know the root or parent ID?  Well that is when recursive T-Sql comes handy!

Here is some SQL to chew on for a bit:

;WITH TeamCTE  (id,managerID,name, [Root])AS(
   --parent
   SELECT id
           ,ManagerID
           ,Name
           ,id
   FROM #Employees e
   WHERE ManagerID IS NULL

   UNION ALL
  
   SELECT e.id
           ,e.ManagerID
           ,e.Name
           ,[Root]
   FROM #Employees e
   INNER JOIN TeamCTE t ON e.ManagerID = t.id
   WHERE e.ManagerID IS NOT NULL
)SELECT * FROM TeamCTE

Pretty neat, right?  Now why don't you go try and create some recursive T-Sql...

Till next time,

Robert

Dynamic Group by without Dynamic SQL

Good After All,

I learned some cool SQL magic the other day! I wanted to dynaamiclly drop columns and group the statement in a store procedure without using dynamic sql.  Dropping the unwanted columns was easy, but the group by statement was not that simple to figure out right away.  In tihs example I will count the number of players and change the columns being outputted.

As always let's create the temp tables with data.  Well use the same values we did a few blogs ago to make things easier.


--Drop Table/store procedure
IF EXISTS ( SELECT  * FROM tempdb.dbo.sysobjects WHERE   ID = OBJECT_ID(N'tempdb..#Teams') ) DROP TABLE #Teams

IF EXISTS ( SELECT  * FROM tempdb.dbo.sysobjects WHERE   ID = OBJECT_ID(N'tempdb..#Players') ) DROP TABLE #Players

IF EXISTS ( SELECT  * FROM tempdb.dbo.sysobjects WHERE   ID = OBJECT_ID(N'tempdb..#TempCount') ) DROP TABLE #TempCount


--Create Table
CREATE TABLE #Teams(TeamID INT,Team VARCHAR(20),Founded DATE,City VARCHAR(30),State CHAR(2))
CREATE TABLE #Players(PlayerID INT,FirstName VARCHAR(20),LastName VARCHAR(20))
CREATE TABLE #Roster(TeamID INT, PlayerID INT)


--Insert Values into Table
INSERT INTO  #Teams(TeamID,Team,Founded,City,State)
    SELECT  100,'Milwaukee Brewers','1970','Milwaukee','WI'
    UNION ALL
    SELECT 200,'Chicago Cubs','1870','chicago','IL'
    UNION ALL
    SELECT 300,'Minnesota Twins','1894','Minneapolis','MN'
    UNION ALL
    SELECT  400,'Detroit Tigers','1894','Detroit','MI'

INSERT INTO #Players( PlayerID, FirstName, LastName )
   SELECT 100,'Hank','Aaron'
   UNION ALL
   SELECT 101, 'Robin','Yount'
   UNION ALL
   SELECT 102,'Ernie','Banks'
   UNION ALL
   SELECT 103,'Billy','Williams'
   UNION ALL
   SELECT 104, 'Kirby','Puckett'
   UNION ALL
   SELECT 105, 'Harmon','Killebrew'
   UNION ALL
   SELECT 106, 'Ty','Cobb'
   UNION ALL
   SELECT 107, 'Al','Kaline'
   
INSERT INTO #Roster( TeamID, PlayerID )
   SELECT 100,100
   UNION ALL
   SELECT 100,101
   UNION ALL
   SELECT 200,102
   UNION ALL
   SELECT 200,103
   UNION ALL
   SELECT 300,104
   UNION ALL
   SELECT 300, 105
   UNION ALL
   SELECT 400, 106
   UNION ALL
   SELECT 400, 107
  




Before we can start diving into the group by we need to set up some variables.  Below are the variables I have as well as defaul the value to 0 if its set to null.

--Declaring the variables
DECLARE @TeamOut BIT
DECLARE @CityOut BITD
ECLARE @StateOut BIT

--assinging values to variables
SET @TeamOut=NULL
SET @CityOut=NULL
SET @StateOut=1

--If values are passed as null then convert to 0
IF ISNULL(@TeamOut,'')          = '' SET @TeamOut=0
IF ISNULL(@CityOut,'')          = '' SET @CityOut=0
IF ISNULL(@StateOut,'')         = '' SET @StateOut=0

Now let's look at the SQL select statement.

SELECT COUNT(p.PlayerID) AS total,
       CASE WHEN @TeamOut = 1 THEN Team ELSE NULL END as Team,
       CASE WHEN @CityOut = 1 THEN City ELSE NULL END as City,
       CASE WHEN @StateOut = 1 THEN State ELSE NULL END as STATE
INTO #TempCount      
FROM #Teams t
INNER JOIN #Roster r ON t.TeamID = r.TeamID
INNER JOIN #Players p ON r.PlayerID = p.PlayerID  
GROUP BY    
   (CASE WHEN @TeamOut=1 THEN Team ELSE NULL END),
   (CASE WHEN @CityOut=1 THEN City ELSE NULL END),
   (CASE WHEN @StateOut=1 THEN State ELSE NULL END)

As you can see I use a case statement with the use of the use of the variables.  This seems to work out perfect.  Now what happens when the bit flag is set to 0 and we dont want to show it?  I thought you would ask that question.  It's pretty simple.  We just modified the temp table (#TempCount) that holds all the data.

--Removing columns we dont want to output
IF @TeamOut         = 0 ALTER TABLE #TempCount DROP COLUMN Team
IF @CityOut         = 0 ALTER TABLE #TempCount DROP COLUMN City
IF @StateOut        = 0 ALTER TABLE #TempCount DROP COLUMN STATE

Now we just do a simple Select statement from the temp table to get the results.  Here are a few different examples with just changing the bit flag.


Example 1.

SET @TeamOut=1
SET @CityOut=NULL
SET @StateOut=NULL

Example 2:

SET @TeamOut=1
SET @CityOut=NULL
SET @StateOut=1







Example 3:

SET @TeamOut=NULL
SET @CityOut=NULL
SET @StateOut=NULL

This is just a simple example for you, but you can see the value is does.  Hope this was valueable to you as it was to me.

Have a great day!

Robert