Get the word count `href` and` nofollow` from each row of a table in SQL Server
I have a table called Posts
that has a column named that HTMLBody
contains html content like href
and nofollow
and I want each one to count every row.[i.e. occurrence]
Expected Result -
What I have tried is below example -
Declare @string varchar(1000)
Set @string = '<p><font color=#000080>Hello Paul,</font></p> <p><font color=#000080>I would try <span><a href="http://www.neuraltherapy.com/" target=_blank rel=nofollow><font color=#003399>www.neuraltherapy.com</font></a> and give them a call to find a practitioner in your area.</span></font></p>'
select (len(@string) - len(replace(@string, 'href', '')))/4 as 'href'
select (len(@string) - len(replace(@string, 'nofollow', '')))/8 as 'nofollow'
Current demo output
How can I get the result as the expected result above. OR any other request would be appreciated. :)
+3
source to share
2 answers
You just want it in one select
, not two:
select @string,
(len(@string) - len(replace(@string, 'href', ''))) / 4 as href
(len(@string) - len(replace(@string, 'nofollow', ''))) / 8 as nofollow
You can, of course, do this for columns in a table as well:
select HTMLBody,
(len(HTMLBody) - len(replace(HTMLBody, 'href', ''))) / 4 as href
(len(HTMLBody) - len(replace(HTMLBody, 'nofollow', ''))) / 8 as nofollow
from Posts;
+4
source to share