February 10, 2012 at 4:04 am
Is the substring function limited to 8000 characters ? No matter what i do, I cant get anymore characters when i use it to extract a substring. The column contains over 35000 characters and my substring should contain 35000 minus the starting point of a specific character
SUBSTRING([Message],CHARINDEX('<', [Message]), DATALENGTH([Message]) - CHARINDEX('<', [Message])) As xmlValue
CHARINDEX('<', [Message]) = 67
DATALENGTH([Message]) = 32984
my substring should be 32917 characters but its always 8000 !
February 10, 2012 at 4:13 am
What data-type is [Message] ?
February 10, 2012 at 4:14 am
its a text column
February 10, 2012 at 4:18 am
To get it out of the way, Text is deprecated so you shouldn't really be using it.
To answer your issue, cast it as VARCHAR(MAX) before doing your substring.
e.g.
SELECT SUBSTRING(CAST([Message] AS VARCHAR(MAX)), CHARINDEX('<', CAST([Message] AS VARCHAR(MAX))),
DATALENGTH(CAST([Message] AS VARCHAR(MAX))) - CHARINDEX('<', CAST([Message] AS VARCHAR(MAX)))) AS xmlValue
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply