Extract between <div> tags

  • I need to extract data from a sharepoint list, where the data is written within div tags.

    I got this so far:

    DECLARE @C varchar(100)

    SET @C = '<div>Sunshine</div>'

    SELECT SUBSTRING(

    @C,

    CHARINDEX('>', @C) + 1,

    LEN(@c) - CHARINDEX('>', @C) - CHARINDEX('<', REVERSE(@c))

    )

    But when I adapt this to my table it says:

    Argument data type ntext is invalid for argument 1 of len function.

    How can i work around this problem?

    thanks in advance!

  • I've used also:

    DECLARE @C varchar(100)

    SET @C = '<div>Sunshine</div>'

    SELECT SUBSTRING(

    @C,

    CHARINDEX('>', @C) + 1,

    DATALENGTH(@c) - CHARINDEX('>', @C) - CHARINDEX('<', REVERSE(@c))

    )

    Error:

    Argument data type ntext is invalid for argument 1 of reverse function.

  • convert the column to nvarchar(max) instead.

    SELECT SUBSTRING(

    CONVERT(NVARCHAR(max),YourColumn),

    CHARINDEX('>', @C) + 1,

    LEN(@c) - CHARINDEX('>', CONVERT(NVARCHAR(max),YourColumn)) - CHARINDEX('<', REVERSE( CONVERT(NVARCHAR(max),YourColumn)))

    FROM YourTable

    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!

  • Lowell (3/27/2013)


    convert the column to nvarchar(max) instead.

    SELECT SUBSTRING(

    CONVERT(NVARCHAR(max),YourColumn),

    CHARINDEX('>', @C) + 1,

    LEN(@c) - CHARINDEX('>', CONVERT(NVARCHAR(max),YourColumn)) - CHARINDEX('<', REVERSE( CONVERT(NVARCHAR(max),YourColumn)))

    FROM YourTable

    Thanks

    But it says the same:

    Argument data type ntext is invalid for argument 1 of len function.

    got anything else in your sleeve?

  • everyplace you have the column represented by @C must be replaced with a convert(nvarchar,@c); so if it's occurring 4 times in your expression, change all four...i think i missed the find/replace for one of them.

    unless you use a CTE to pre-cast it for you..../. i love doing that

    with MyCTE as

    (

    select convert(nverchar(max),YourColumn) as YourColumn From YourTable

    )

    ..do the work here

    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!

  • Lowell (3/27/2013)


    everyplace you have the column represented by @C must be replaced with a convert(nvarchar,@c); so if it's occurring 4 times in your expression, change all four...i think i missed the find/replace for one of them.

    unless you use a CTE to pre-cast it for you..../. i love doing that

    with MyCTE as

    (

    select convert(nverchar(max),YourColumn) as YourColumn From YourTable

    )

    ..do the work here

    Thanks!!

    SELECT SUBSTRING(

    CONVERT(NVARCHAR(max),YourColumn),

    CHARINDEX('>', @C) + 1,

    LEN(CONVERT(nvarchar(max), (@c) - CHARINDEX('>', CONVERT(NVARCHAR(max),YourColumn)) - CHARINDEX('<', REVERSE( CONVERT(NVARCHAR(max),YourColumn)))

    FROM YourTable

  • I hate to say it but why not something simple like:

    DECLARE @C varchar(100)

    SET @C = '<div>Sunshine</div>'

    SELECT REPLACE(replace(@c,'</div>',''),'<div>','')

  • Erin Ramsay (3/27/2013)


    I hate to say it but why not something simple like:

    DECLARE @C varchar(100)

    SET @C = '<div>Sunshine</div>'

    SELECT REPLACE(replace(@c,'</div>',''),'<div>','')

    O_o !? SHOW OFFFFFFFF!

    Thanks!

Viewing 8 posts - 1 through 7 (of 7 total)

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