Extract between <div> tags

• davdam8

  Hall of Fame

  Points: 3620

  More actions

  March 27, 2013 at 9:37 am

  #270468

  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!

• davdam8

  Hall of Fame

  Points: 3620

  More actions

  March 27, 2013 at 9:41 am

  #1601222

  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.

• Lowell

  SSC Guru

  Points: 323518

  More actions

  March 27, 2013 at 9:43 am

  #1601226

  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!

• davdam8

  Hall of Fame

  Points: 3620

  More actions

  March 27, 2013 at 9:49 am

  #1601231

  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?

• Lowell

  SSC Guru

  Points: 323518

  More actions

  March 27, 2013 at 9:53 am

  #1601233

  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!

• davdam8

  Hall of Fame

  Points: 3620

  More actions

  March 27, 2013 at 10:22 am

  #1601248

  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

• Erin Ramsay

  SSCrazy Eights

  Points: 9728

  More actions

  March 27, 2013 at 10:35 am

  #1601254

  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>','')

• davdam8

  Hall of Fame

  Points: 3620

  More actions

  March 27, 2013 at 10:44 am

  #1601258

  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!