May 1, 2014 at 3:57 am
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>
May 1, 2014 at 5:30 am
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
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy