Finding Position Plain text inside html markup using SQL

  • 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>

  • 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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply