Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Finding Position Plain text inside html markup using SQL Expand / Collapse
Author
Message
Posted Thursday, May 1, 2014 3:57 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 7, 2014 12:46 AM
Points: 5, Visits: 13
Consider following string

I would like to thank whomever did the proofreading on

Consider following html content

Now i want to find exact position of mentioned string using sql

HTML Content:

<p dir=\""ltr"\" id=\""docs-internal-guid-1cf680b0-6797-3c8c-9506-d004e081bd4f"\" style=\""line-height:1.15;margin-top:0pt;margin-bottom:0pt;"\">\r\t<span style=\""font-size:14px;"\"><span style=\""font-family:\"><span font-style:=\"\" font-variant:=\"\" style=\""font-weight:\" text-decoration:=\"\" vertical-align:=\"\">I chose ILP because your website was the 2nd hit on Google, which got my attention, and your pricing was extremely competitive. Utilizing your service was really a shot in the dark. Fortunately, it was a great decision. I would like to thank whomever<span style=\""font-family:courier\"> did the proofreading on</span> my daughter's decorative and fine arts dissertation. Not only was this proofreader's work thorough but also amazingly fast. Final product has to be printed and hard bound by this coming Monday which I was not fully convinced was possible 36 hours ago. She has one shot at this, no process for corrections, no drafts. Your service made a difference in my daughter's life.</span></span></span></p>\r<p dir=\""ltr"\" style=\""line-height:1.15;margin-top:0pt;margin-bottom:0pt;"\">\r\t </p>\r<p dir=\""ltr"\" style=\""line-height:1.15;margin-top:0pt;margin-bottom:0pt;"\">\r\t<span style=\""font-size:14px;"\"><span style=\""font-family:\"><span font-style:=\"\" font-variant:=\"\" style=\""font-weight:\" text-decoration:=\"\" vertical-align:=\"\">Added some testing content that will display on click on read more.....</span></span></span></p>\r<p dir=\""ltr"\" style=\""line-height:1.15;margin-top:0pt;margin-bottom:0pt;"\">\r\t<span style=\""font-size:14px;"\"><span style=\""font-family:\"><span font-style:=\"\" font-variant:=\"\" style=\""font-weight:\" text-decoration:=\"\" vertical-align:=\"\">also added testing data that will display on click read more....hgsdhgfg</span></span></span>
Post #1566662
Posted Thursday, May 1, 2014 5:30 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:09 PM
Points: 12,905, Visits: 32,166
there's probably more to the question, but the charindex function finds the starting position of a string within another string.
DECLARE @FindPharase varchar(max) =' I would like to thank whomever<span style=\""font-family:courier\"> did the proofreading on'
;WITH MyCTE([HTMLFragment])
AS
(
SELECT '<p dir=\""ltr"\" id=\""docs-internal-guid-1cf680b0-6797-3c8c-9506-d004e081bd4f"\" style=\""line-height:1.15;margin-top:0pt;margin-bottom:0pt;"\">\r\t<span style=\""font-size:14px;"\"><span style=\""font-family:\"><span font-style:=\"\" font-variant:=\"\" style=\""font-weight:\" text-decoration:=\"\" vertical-align:=\"\">I chose ILP because your website was the 2nd hit on Google, which got my attention, and your pricing was extremely competitive. Utilizing your service was really a shot in the dark. Fortunately, it was a great decision. I would like to thank whomever<span style=\""font-family:courier\"> did the proofreading on</span> my daughter''s decorative and fine arts dissertation. Not only was this proofreader''s work thorough but also amazingly fast. Final product has to be printed and hard bound by this coming Monday which I was not fully convinced was possible 36 hours ago. She has one shot at this, no process for corrections, no drafts. Your service made a difference in my daughter''s life.</span></span></span>\r<p dir=\""ltr"\" style=\""line-height:1.15;margin-top:0pt;margin-bottom:0pt;"\">\r\t \r<p dir=\""ltr"\" style=\""line-height:1.15;margin-top:0pt;margin-bottom:0pt;"\">\r\t<span style=\""font-size:14px;"\"><span style=\""font-family:\"><span font-style:=\"\" font-variant:=\"\" style=\""font-weight:\" text-decoration:=\"\" vertical-align:=\"\">Added some testing content that will display on click on read more.....</span></span></span>\r<p dir=\""ltr"\" style=\""line-height:1.15;margin-top:0pt;margin-bottom:0pt;"\">\r\t<span style=\""font-size:14px;"\"><span style=\""font-family:\"><span font-style:=\"\" font-variant:=\"\" style=\""font-weight:\" text-decoration:=\"\" vertical-align:=\"\">also added testing data that will display on click read more....hgsdhgfg</span></span></span> ' UNION ALL
SELECT 'Some Extra Data to show it''s finding it in another row ad a different location.<p dir=\""ltr"\" id=\""docs-internal-guid-1cf680b0-6797-3c8c-9506-d004e081bd4f"\" style=\""line-height:1.15;margin-top:0pt;margin-bottom:0pt;"\">\r\t<span style=\""font-size:14px;"\"><span style=\""font-family:\"><span font-style:=\"\" font-variant:=\"\" style=\""font-weight:\" text-decoration:=\"\" vertical-align:=\"\">I chose ILP because your website was the 2nd hit on Google, which got my attention, and your pricing was extremely competitive. Utilizing your service was really a shot in the dark. Fortunately, it was a great decision. I would like to thank whomever<span style=\""font-family:courier\"> did the proofreading on</span> my daughter''s decorative and fine arts dissertation. Not only was this proofreader''s work thorough but also amazingly fast. Final product has to be printed and hard bound by this coming Monday which I was not fully convinced was possible 36 hours ago. She has one shot at this, no process for corrections, no drafts. Your service made a difference in my daughter''s life.</span></span></span>\r<p dir=\""ltr"\" style=\""line-height:1.15;margin-top:0pt;margin-bottom:0pt;"\">\r\t \r<p dir=\""ltr"\" style=\""line-height:1.15;margin-top:0pt;margin-bottom:0pt;"\">\r\t<span style=\""font-size:14px;"\"><span style=\""font-family:\"><span font-style:=\"\" font-variant:=\"\" style=\""font-weight:\" text-decoration:=\"\" vertical-align:=\"\">Added some testing content that will display on click on read more.....</span></span></span>\r<p dir=\""ltr"\" style=\""line-height:1.15;margin-top:0pt;margin-bottom:0pt;"\">\r\t<span style=\""font-size:14px;"\"><span style=\""font-family:\"><span font-style:=\"\" font-variant:=\"\" style=\""font-weight:\" text-decoration:=\"\" vertical-align:=\"\">also added testing data that will display on click read more....hgsdhgfg</span></span></span> '
)
SELECT charindex(@FindPharase,HTMLFragment),* FROM MyCTE;

is the question "i want to find a phrase even if portions of it contain html tags?
that's a little more difficult, since you essentially have to strip out the html.
is that the question?


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1566674
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse