I have a sql field that contain html code that contain data in <p>,<ul> etc, need a way to extract the data before first <img> tag out of it.
any ideas?
?
Below is one of the html data for example:
<p><span lang="EN-GB"><span style="font-family: arial; font-size: 12px;">The Samsung Wave M allows users to keep up to date with their hectic social lives via ChatON and Social Hub. With a wide 3.65-inch HVGA screen made from tempered glass and an eye-catching metallic body, the Wave M enables users to stay socially connected on the move. Streamlined messaging feeds, enhanced on-the-go web browsing and Wi-Fi applications help deliver a seamless mobile experience. Wi-Fi Direct and NFC add further functionality. <br /></span></span></p> <p><img src="http://www.isellmobile.co.uk/images/manufacturers/samsung/2011/Wave_M_Product_Image_1.jpg" alt="" width="400" height="400" /></p> <p> </p> <hr /> <p> </p> <h3>The facts</h3> <p><strong>Official specs</strong></p> <ul> <li><strong>OS</strong>: Bada 2.0</li> <li><strong>Processor</strong>: 832MHz processor</li> <li><strong>Camera:</strong> 5 megapixels with auto-focus and LED Flash</li> <li><strong>Storage</strong>: 150MB internal memory plus 2GB in box and microSD slot (up to 32GB)</li> <li><strong>Screen</strong>: <span lang="EN-GB"><span style="font-size: 12px;">3.6-inch HVGA TFT-LCD<br /></span></span></li> <li><strong>Dimensions</strong>: 113.8x63.3x12.2mm </li> <li><strong>Weight: </strong>121g</li> <li><strong>Standby</strong>:<span lang="EN-GB"><span style="font-size: 12px;"> Up to 480 hours </span></span></li> <li><strong>Talktime</strong>: 6.5 hours</li> </ul> <p><strong>What’s new</strong></p> <ul> <li>Bada 2.0 OS</li> <li>Samsung TouchWiz</li> <li>Samsung ChatON mobile communication service</li> <li>NFC</li> <li><span lang="EN-GB"><span style="font-family: arial; font-size: 12px;"> Wi-Fi Direct<br /></span></span></li> <li><span lang="EN-GB"><span style="font-family: arial; font-size: 12px;">Samsung Kies air<br /></span></span></li> <li><span lang="EN-GB"><span style="font-family: arial; font-size: 12px;">Samsung apps<br /></span></span></li> </ul>
DECLARE @Value VARCHAR(MAX) = '<p><span lang="EN-GB"><span style="font-family: arial; font-size: 12px;">The Samsung Wave M allows users to keep up to date with their hectic social lives via ChatON and Social Hub. With a wide 3.65-inch HVGA screen made from tempered glass and an eye-catching metallic body, the Wave M enables users to stay socially connected on the move. Streamlined messaging feeds, enhanced on-the-go web browsing and Wi-Fi applications help deliver a seamless mobile experience. Wi-Fi Direct and NFC add further functionality. <br /></span></span></p> <p><img src="http://www.isellmobile.co.uk/images/manufacturers/samsung/2011/Wave_M_Product_Image_1.jpg" alt="" width="400" height="400" /></p> <p> </p> <hr /> <p> </p> <h3>The facts</h3> <p><strong>Official specs</strong></p> <ul> <li><strong>OS</strong>: Bada 2.0</li> <li><strong>Processor</strong>: 832MHz processor</li> <li><strong>Camera:</strong> 5 megapixels with auto-focus and LED Flash</li> <li><strong>Storage</strong>: 150MB internal memory plus 2GB in box and microSD slot (up to 32GB)</li> <li><strong>Screen</strong>: <span lang="EN-GB"><span style="font-size: 12px;">3.6-inch HVGA TFT-LCD<br /></span></span></li> <li><strong>Dimensions</strong>: 113.8x63.3x12.2mm </li> <li><strong>Weight: </strong>121g</li> <li><strong>Standby</strong>:<span lang="EN-GB"><span style="font-size: 12px;"> Up to 480 hours </span></span></li> <li><strong>Talktime</strong>: 6.5 hours</li> </ul> <p><strong>What’s new</strong></p> <ul> <li>Bada 2.0 OS</li> <li>Samsung TouchWiz</li> <li>Samsung ChatON mobile communication service</li> <li>NFC</li> <li><span lang="EN-GB"><span style="font-family: arial; font-size: 12px;"> Wi-Fi Direct<br /></span></span></li> <li><span lang="EN-GB"><span style="font-family: arial; font-size: 12px;">Samsung Kies air<br /></span></span></li> <li><span lang="EN-GB"><span style="font-family: arial; font-size: 12px;">Samsung apps<br /></span></span></li> </ul>'
SELECT LEFT(@Value, PATINDEX('%<img%', @Value)-1)
DECLARE @Value VARCHAR(MAX) = '<p><span lang="EN-GB"><span style="font-family: arial; font-size: 12px;">The Samsung Wave M allows users to keep up to date with their hectic social lives via ChatON and Social Hub. With a wide 3.65-inch HVGA screen made from tempered glass and an eye-catching metallic body, the Wave M enables users to stay socially connected on the move. Streamlined messaging feeds, enhanced on-the-go web browsing and Wi-Fi applications help deliver a seamless mobile experience. Wi-Fi Direct and NFC add further functionality. <br /></span></span></p> <p><img src="http://www.isellmobile.co.uk/images/manufacturers/samsung/2011/Wave_M_Product_Image_1.jpg" alt="" width="400" height="400" /></p> <p> </p> <hr /> <p> </p> <h3>The facts</h3> <p><strong>Official specs</strong></p> <ul> <li><strong>OS</strong>: Bada 2.0</li> <li><strong>Processor</strong>: 832MHz processor</li> <li><strong>Camera:</strong> 5 megapixels with auto-focus and LED Flash</li> <li><strong>Storage</strong>: 150MB internal memory plus 2GB in box and microSD slot (up to 32GB)</li> <li><strong>Screen</strong>: <span lang="EN-GB"><span style="font-size: 12px;">3.6-inch HVGA TFT-LCD<br /></span></span></li> <li><strong>Dimensions</strong>: 113.8x63.3x12.2mm </li> <li><strong>Weight: </strong>121g</li> <li><strong>Standby</strong>:<span lang="EN-GB"><span style="font-size: 12px;"> Up to 480 hours </span></span></li> <li><strong>Talktime</strong>: 6.5 hours</li> </ul> <p><strong>What’s new</strong></p> <ul> <li>Bada 2.0 OS</li> <li>Samsung TouchWiz</li> <li>Samsung ChatON mobile communication service</li> <li>NFC</li> <li><span lang="EN-GB"><span style="font-family: arial; font-size: 12px;"> Wi-Fi Direct<br /></span></span></li> <li><span lang="EN-GB"><span style="font-family: arial; font-size: 12px;">Samsung Kies air<br /></span></span></li> <li><span lang="EN-GB"><span style="font-family: arial; font-size: 12px;">Samsung apps<br /></span></span></li> </ul>'
SELECT LEFT(@Value, PATINDEX('%<img%', @Value)-1)
How do i update the output of the wuery above to save in a New column called OverViewNew?
Marked as answer by sheraz_aries on Feb 20, 2012 11:32 AM
sheraz_aries
Member
277 Points
618 Posts
Everything before <img> tag from a sql field
Feb 20, 2012 09:40 AM|LINK
I have a sql field that contain html code that contain data in <p>,<ul> etc, need a way to extract the data before first <img> tag out of it.
any ideas?
?
Below is one of the html data for example:
D J
Contributor
5362 Points
941 Posts
Re: Everything before <img> tag from a sql field
Feb 20, 2012 09:47 AM|LINK
DECLARE @Value VARCHAR(MAX) = '<p><span lang="EN-GB"><span style="font-family: arial; font-size: 12px;">The Samsung Wave M allows users to keep up to date with their hectic social lives via ChatON and Social Hub. With a wide 3.65-inch HVGA screen made from tempered glass and an eye-catching metallic body, the Wave M enables users to stay socially connected on the move. Streamlined messaging feeds, enhanced on-the-go web browsing and Wi-Fi applications help deliver a seamless mobile experience. Wi-Fi Direct and NFC add further functionality. <br /></span></span></p> <p><img src="http://www.isellmobile.co.uk/images/manufacturers/samsung/2011/Wave_M_Product_Image_1.jpg" alt="" width="400" height="400" /></p> <p> </p> <hr /> <p> </p> <h3>The facts</h3> <p><strong>Official specs</strong></p> <ul> <li><strong>OS</strong>: Bada 2.0</li> <li><strong>Processor</strong>: 832MHz processor</li> <li><strong>Camera:</strong> 5 megapixels with auto-focus and LED Flash</li> <li><strong>Storage</strong>: 150MB internal memory plus 2GB in box and microSD slot (up to 32GB)</li> <li><strong>Screen</strong>: <span lang="EN-GB"><span style="font-size: 12px;">3.6-inch HVGA TFT-LCD<br /></span></span></li> <li><strong>Dimensions</strong>: 113.8x63.3x12.2mm </li> <li><strong>Weight: </strong>121g</li> <li><strong>Standby</strong>:<span lang="EN-GB"><span style="font-size: 12px;"> Up to 480 hours </span></span></li> <li><strong>Talktime</strong>: 6.5 hours</li> </ul> <p><strong>What’s new</strong></p> <ul> <li>Bada 2.0 OS</li> <li>Samsung TouchWiz</li> <li>Samsung ChatON mobile communication service</li> <li>NFC</li> <li><span lang="EN-GB"><span style="font-family: arial; font-size: 12px;"> Wi-Fi Direct<br /></span></span></li> <li><span lang="EN-GB"><span style="font-family: arial; font-size: 12px;">Samsung Kies air<br /></span></span></li> <li><span lang="EN-GB"><span style="font-family: arial; font-size: 12px;">Samsung apps<br /></span></span></li> </ul>' SELECT LEFT(@Value, PATINDEX('%<img%', @Value)-1)sheraz_aries
Member
277 Points
618 Posts
Re: Everything before <img> tag from a sql field
Feb 20, 2012 10:09 AM|LINK
DECLARE @Value VARCHAR(MAX) = '<p><span lang="EN-GB"><span style="font-family: arial; font-size: 12px;">The Samsung Wave M allows users to keep up to date with their hectic social lives via ChatON and Social Hub. With a wide 3.65-inch HVGA screen made from tempered glass and an eye-catching metallic body, the Wave M enables users to stay socially connected on the move. Streamlined messaging feeds, enhanced on-the-go web browsing and Wi-Fi applications help deliver a seamless mobile experience. Wi-Fi Direct and NFC add further functionality. <br /></span></span></p> <p><img src="http://www.isellmobile.co.uk/images/manufacturers/samsung/2011/Wave_M_Product_Image_1.jpg" alt="" width="400" height="400" /></p> <p> </p> <hr /> <p> </p> <h3>The facts</h3> <p><strong>Official specs</strong></p> <ul> <li><strong>OS</strong>: Bada 2.0</li> <li><strong>Processor</strong>: 832MHz processor</li> <li><strong>Camera:</strong> 5 megapixels with auto-focus and LED Flash</li> <li><strong>Storage</strong>: 150MB internal memory plus 2GB in box and microSD slot (up to 32GB)</li> <li><strong>Screen</strong>: <span lang="EN-GB"><span style="font-size: 12px;">3.6-inch HVGA TFT-LCD<br /></span></span></li> <li><strong>Dimensions</strong>: 113.8x63.3x12.2mm </li> <li><strong>Weight: </strong>121g</li> <li><strong>Standby</strong>:<span lang="EN-GB"><span style="font-size: 12px;"> Up to 480 hours </span></span></li> <li><strong>Talktime</strong>: 6.5 hours</li> </ul> <p><strong>What’s new</strong></p> <ul> <li>Bada 2.0 OS</li> <li>Samsung TouchWiz</li> <li>Samsung ChatON mobile communication service</li> <li>NFC</li> <li><span lang="EN-GB"><span style="font-family: arial; font-size: 12px;"> Wi-Fi Direct<br /></span></span></li> <li><span lang="EN-GB"><span style="font-family: arial; font-size: 12px;">Samsung Kies air<br /></span></span></li> <li><span lang="EN-GB"><span style="font-family: arial; font-size: 12px;">Samsung apps<br /></span></span></li> </ul>' SELECT LEFT(@Value, PATINDEX('%<img%', @Value)-1)How do i update the output of the wuery above to save in a New column called OverViewNew?
D J
Contributor
5362 Points
941 Posts
Re: Everything before <img> tag from a sql field
Feb 20, 2012 11:14 AM|LINK
SELECT OverView, LEFT(OverView, PATINDEX('%<img%', OverView)-1) AS OverViewNew FROM TableNamesheraz_aries
Member
277 Points
618 Posts
Re: Everything before <img> tag from a sql field
Feb 20, 2012 11:58 AM|LINK
Getting error:
Invalid length parameter passed to the LEFT or SUBSTRING function.
D J
Contributor
5362 Points
941 Posts
Re: Everything before <img> tag from a sql field
Feb 22, 2012 03:37 AM|LINK
So you marked your own post as answer :D
I thought you can fix it, Need to use case here
SELECT OverView, LEFT(OverView, CASE WHEN CHARINDEX('<img', OverView)= 0 THEN 0 ELSE CHARINDEX('<img', OverView)-1 END )AS OverViewNew FROM TableName