For my opinion, this is not for what a database is developed.
My guess here is, search for a tag which is not removed, then search backwards for
<p
save this patindex, search forward to
</p>
, now you can delete all between the patindex. (don`t forget to calculate the searchstrings here)
Another approach could be:
If you have to deal with such task, you can easy do this in frontend with JavaScript/jQuery.
$('p').each(function( index ) {if($(this).html() === '') { this.remove()}})
This will stripe out all paragraph which are empty.
EDIT:
Loop thru the html and select every paragraph into a temp table.
Then you can handle it easy.
declare
@tbl TABLE(
[ID] [int] IDENTITY(1,1) NOT NULL,
[paragraph] nvarchar(max) NOT NULL
);
declare
@HtmlLen INT = LEN(@Html)
,@StartIndex INT = 0
,@EndIndex INT
WHILE CHARINDEX('<p', @Html)>0
BEGIN
SELECT
@StartIndex = CHARINDEX('<p', @Html),
@EndIndex = CHARINDEX('</p>', @Html)
INSERT INTO @tbl([paragraph])
select SUBSTRING ( @Html ,@StartIndex, @EndIndex -4 )
select @Html = SUBSTRING(@Html, @EndIndex+3, @HtmlLen)
END
select * from @tbl
the result
you can rebuild your html string with (for SQL2017+)
-- at least build up your string again
SELECT STRING_AGG([paragraph], '') FROM @tbl
Post has been edited because the phrasing and structure of the original post was asking for the work to be done for me as opposed to the best method to complete the work.
Problem
Email template (shown below) contains tags (encapsulated in ‘$’ characters), these tags are replaced with relevant data (if not null) via a SQL Server trigger. After processing, there may be instances where the tags are not replaced.
I could just replace the tags with empty varchars, but this will cause the email to contain large areas of whites space when rendered in an email client or the browser.
Embedding JavaScript into the email is a consideration for later development but that is outside the scope of what I am doing. This has to be achieved purely through SQL.
Question
What is a more intelligent way to achieve the same results?
Tags
$ChargeAmount_Changed$
$StartDate_Changed$
$EndDate_Changed$
$EscalationAmount_Changed$
$AccountCode_Changed$
$COSAccountCode_Changed$
$InvoiceDesc_Changed$
$BillingPeriod_Changed$
$BillingCycle_Changed$
$FinanceParty_Changed$
$FinanceAmount_Changed$
$BillingCustomerCode_Changed$
$ChargeAmount_Crtitical$
$StartDate_Critical$
$EndDate_Critical$
$EscalationAmount_Critical$
$AccountCode_Critical$
$COSAccountCode_Critical$
$InvoiceDescription_Critical$
$BillingPeriod_Critical$
$BillingCycle_Critical$
$FinanceParty_Critical$
$FinanceAmount_Critical$
$BillingCustomerCode_Critical$
Code
CREATE FUNCTION fn_bpo_SALSEmail_RemoveTags(
@Html VARCHAR(MAX)
, @EmailFlag VARCHAR(50)
)
RETURNS VARCHAR(MAX)
WITH ENCRYPTION
AS
BEGIN
DECLARE @RowID INT
, @Tag VARCHAR(50)
, @StartIndex INT = 0
, @EndIndex INT
, @StartTag VARCHAR(50)
, @EndTag VARCHAR(50)
, @RowVar VARCHAR (MAX)
, @DeterminedRow INT = 0
DECLARE @Tags TABLE (
fldTagID INT IDENTITY (1, 1) PRIMARY KEY
, fldTag VARCHAR(50) NOT NULL
);
INSERT INTO @Tags
SELECT fldTag
FROM tblSALSEmailLoopingFields WITH (NOLOCK)
WHERE fldEmailFlag = @EmailFlag
SELECT @RowID = COUNT(fldTag) FROM @Tags
WHILE @RowID <> 0
BEGIN
SET @DeterminedRow = 0
SELECT @Tag = fldTag
FROM @Tags
WHERE fldTagID = @RowID
SET @StartIndex = PATINDEX('%' + @Tag +'%', @Html)
SET @EndIndex = @StartIndex + LEN(@Tag)
-- Expression found
IF @StartIndex > 0
BEGIN
-- Have not found the whole row yet
WHILE @DeterminedRow = 0
BEGIN
--See if the index is at the start of the opening paragraph element for the row
SET @StartTag = SUBSTRING(@Html, @StartIndex, LEN('<p'))
--See if the index is at the end of the closing paragraph element for the row
SET @EndTag = SUBSTRING(@Html, @EndIndex, LEN('</p>'))
--March the start index back
IF @StartTag <> '<p'
BEGIN
SET @StartIndex = @StartIndex - 1
END
--March the end index forward
IF @EndTag <> '</p>'
BEGIN
SET @EndIndex = @EndIndex + 1
END
-- Found the whole row! replace with empty var
IF @StartTag = '<p' AND @EndTag = '</p>'
BEGIN
SET @DeterminedRow = 1
-- Get the whole row
SET @RowVar = SUBSTRING(@Html, @StartIndex, @EndIndex - @StartIndex + LEN(@EndTag))
SET @Html = REPLACE(@Html, @RowVar, '')
END
END
END
SET @Html = REPLACE(@Html, @Tag, '')
SET @RowID = @RowID - 1
END
RETURN @Html
END
HTML Template
-- Start of template
<!DOCTYPE html
PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html >
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title> </title>
<style type="text/css">
.cs2654AE3A {
text-align: left;
text-indent: 0pt;
margin: 0pt 0pt 0pt 0pt
}
.cs37BA8FCA {
color: #000000;
background-color: transparent;
font-family: Verdana;
font-size: 9pt;
font-weight: normal;
font-style: normal;
}
</style>
</head>
<body>
<p class="cs2654AE3A"><span class="cs37BA8FCA">A contract item fee has been ??fldAction?? on Contract No.
??fldContractNo?? , for customer ??fldCustomerCode?? .</span></p>
<p class="cs2654AE3A"><span class="cs37BA8FCA">The fee ??fldAction?? is ??fldFeeType??</span></p>
<p class="cs2654AE3A"><span class="cs37BA8FCA">Located on ??fldItemType?? : ??fldItemCode??</span></p>
<p class="cs2654AE3A"><span class="cs37BA8FCA"> </span></p>
<p class="cs2654AE3A"><span class="cs37BA8FCA">$ChargeAmount_Changed$</span></p>
<p class="cs2654AE3A"><span class="cs37BA8FCA">$ChargeAmount_Crtitical$</span></p>
<p class="cs2654AE3A"><span class="cs37BA8FCA"> </span></p>
<p class="cs2654AE3A"><span class="cs37BA8FCA">$StartDate_Changed$</span></p>
<p class="cs2654AE3A"><span class="cs37BA8FCA">$StartDate_Critical$</span></p>
<p class="cs2654AE3A"><span class="cs37BA8FCA"> </span></p>
<p class="cs2654AE3A"><span class="cs37BA8FCA">$EndDate_Changed$</span></p>
<p class="cs2654AE3A"><span class="cs37BA8FCA">$EndDate_Critical$</span></p>
<p class="cs2654AE3A"><span class="cs37BA8FCA"> </span></p>
<p class="cs2654AE3A"><span class="cs37BA8FCA">$EscalationAmount_Changed$</span></p>
<p class="cs2654AE3A"><span class="cs37BA8FCA">$EscalationAmount_Critical$</span></p>
<p class="cs2654AE3A"><span class="cs37BA8FCA"> </span></p>
<p class="cs2654AE3A"><span class="cs37BA8FCA">$AccountCode_Changed$</span></p>
<p class="cs2654AE3A"><span class="cs37BA8FCA">$AccountCode_Critical$</span></p>
<p class="cs2654AE3A"><span class="cs37BA8FCA"> </span></p>
<p class="cs2654AE3A"><span class="cs37BA8FCA">$COSAccountCode_Changed$</span></p>
<p class="cs2654AE3A"><span class="cs37BA8FCA">$COSAccountCode_Critical$</span></p>
<p class="cs2654AE3A"><span class="cs37BA8FCA"> </span></p>
<p class="cs2654AE3A"><span class="cs37BA8FCA">$InvoiceDesc_Changed$</span></p>
<p class="cs2654AE3A"><span class="cs37BA8FCA">$InvoiceDescription_Critical$</span></p>
<p class="cs2654AE3A"><span class="cs37BA8FCA"> </span></p>
<p class="cs2654AE3A"><span class="cs37BA8FCA">$BillingPeriod_Changed$</span></p>
<p class="cs2654AE3A"><span class="cs37BA8FCA">$BillingPeriod_Critical$</span></p>
<p class="cs2654AE3A"><span class="cs37BA8FCA"> </span></p>
<p class="cs2654AE3A"><span class="cs37BA8FCA">$BillingCycle_Changed$</span></p>
<p class="cs2654AE3A"><span class="cs37BA8FCA">$BillingCycle_Critical$</span></p>
<p class="cs2654AE3A"><span class="cs37BA8FCA"> </span></p>
<p class="cs2654AE3A"><span class="cs37BA8FCA">$FinanceParty_Changed$</span></p>
<p class="cs2654AE3A"><span class="cs37BA8FCA">$FinanceParty_Critical$</span></p>
<p class="cs2654AE3A"><span class="cs37BA8FCA"> </span></p>
<p class="cs2654AE3A"><span class="cs37BA8FCA">$FinanceAmount_Changed$</span></p>
<p class="cs2654AE3A"><span class="cs37BA8FCA">$FinanceAmount_Critical$</span></p>
<p class="cs2654AE3A"><span class="cs37BA8FCA"> </span></p>
<p class="cs2654AE3A"><span class="cs37BA8FCA">$BillingCustomerCode_Changed$</span></p>
<p class="cs2654AE3A"><span class="cs37BA8FCA">$BillingCustomerCode_Critical$</span></p>
<p class="cs2654AE3A"><span class="cs37BA8FCA"> </span></p>
<p class="cs2654AE3A"><span class="cs37BA8FCA">This change was made by : ??fldEmpFullName??</span></p>
<p class="cs2654AE3A"><span class="cs37BA8FCA"> </span></p>
</body>
</html>
-- End of template
Actually I am not. At the desk, but I guess there might be a second solution. I will have a look later
