- Open SQL Management Studio
- Expand your test CRM database table view
- Expand your test CRM Org table
- Expand Programmibility
- Expand Functions
- Right-Click Scalar-valued Functions
- Select New Scalar-valued Function
- I use Scalar in this example because I was able to create a new function in this folder. You can use a different Function folder if you prefer.
- Replace the default function with the function below beginning with the word Create Function and ending with the Word Go.
- Do not replace the verbiage above the Author, Create Date or Description. See the screen shot below for reference
Default Function Replaced with New Function
- Execute the Query
- Refresh the SQL Data Object view
- Follow the steps above to expand the Programmability folder
- Expand the Scalar-valued Function folder
- Scroll to the bottom of the list
- Right-click the function named dbo.udf_StripHTML and select Properties
- Select Permissions from the left navigation link
- Select the Add button, add the ‘CRMReaderRole’ and give the Role execute rights
- Save your changes
The function is now ready for use. To use the function in a query, simply call the function in the select query. You’ll place the function name before the field that contains the html tags that you want to remove. The name of the function is dbo.udf_StripHTML. Here is an example of a simple query using the function"
- select activityid, activitytypecodename, createdon, dbo.udf_StripHTML(description), owneridname from filteredactivitypointer
When you run the query, you’ll find that the description field for e-mail type activities now returns the field value minus the html tags, well, almost minus. I did find that the function misses a couple of tags like ‘ ’ and a few others. I guess my next step will be to read a quick tutorial on writing SQL functions and I will see if I can modify the function to remove the few remaining html characters. Better yet, feel free to post an improved function as a response to this post and I will be happy to use yours.
Function Code
CREATE FUNCTION [dbo].[udf_StripHTML]
(@HTMLText VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @Start INT
DECLARE @End INT
DECLARE @Length INT
SET @Start = CHARINDEX(‘<‘,@HTMLText)
SET @End = CHARINDEX(‘>’,@HTMLText,CHARINDEX(‘<‘,@HTMLText))
SET @Length = (@End – @Start) + 1
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
RETURN LTRIM(RTRIM(@HTMLText))
END
GO
