Post reply

Filtering out characters with substring

  • June 3, 2016 at 10:19 am

    #314357

    I have a database field that contains an embed url. This field also wants contains the iframe tags. I've been trying to filter it out so just to keep the source url. It has been partially successful, in that it removes the beginning part, but I am seeming to have problems at the end part. Here is the code:

    DECLARE @String varchar(512) ='<iframe src="http://player.vimeo.com/video/3782iiii?title=0&byline=0&portrait=0&autoplay=1" width="704" height="396" frameborder="0" webkitAllowFullScreen mozallowfullscreen allowFullScreen></iframe>'

    SELECT SUBSTRING(@String,CharINDEX('src="',@String)+5, Charindex('"',@String,CharINDEX('src="',@String)+5)) AS FilteredString

    ,CHARINDEX(CHAR(34), @String, (CHARINDEX(CHAR(34), @String)+1)) as SecondQuote

    ,CHARINDEX('a', @String, (CHARINDEX('a', @String)+1)) AS SecondA

    This is the result I get:

    FilteredString:

    http://player.vimeo.com/video/3782iiii?title=0&byline=0&portrait=0&autoplay=1" width="704"

    SecondQuote:

    103

    SecondA:

    23

    SecondA was more of a test and it is properly getting the second instance of the letter. SecondQuote should have gotten the next instance from the url, but that is not working. I am not sure as to why it is not working with the quotes.

  • June 3, 2016 at 10:49 am

    #1883159

    I would approach this kind of thing as a Regex and use CLR functionality for it.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • June 3, 2016 at 10:49 am

    #1883160

    It's essentially an XML fragment. Why don't you treat it as such?

    DECLARE @String varchar(512) ='<iframe src="http://player.vimeo.com/video/3782iiii?title=0&byline=0&portrait=0&autoplay=1" width="704" height="396" frameborder="0" webkitAllowFullScreen mozallowfullscreen allowFullScreen></iframe>'

    SELECT CAST(REPLACE(REPLACE(@String, '&', '&'), 'AllowFullScreen', 'AllowFullScreen="true"') AS XML).value('/iframe[1]/@src', 'NVARCHAR(100)')

    I had to replace the & with &amp; and had to specify ="true" for some of the attributes to get it to cast as XML.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

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

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