Extract between <div> tags

Question

Post reply

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!

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

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

davdam8 Hall of Fame Points: 3620 More actions · Answer 1

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: 323546 More actions · Answer 2

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 · Answer 3

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: 323546 More actions · Answer 4

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 · Answer 5

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 · Answer 6

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 · Answer 7

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!