March 27, 2013 at 9:37 am
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!
March 27, 2013 at 9:43 am
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
March 27, 2013 at 9:49 am
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?
March 27, 2013 at 9:53 am
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
March 27, 2013 at 10:22 am
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply