1

I'm using the following snippet:

SELECT ID, abc = STUFF
(
    (
        SELECT ',' + name
        FROM temp1 As T2
        WHERE T2.ID = T1.ID
        FOR XML PATH (''), TYPE
    ).value('(./text())[1]', 'varchar(max)')
, 1, 1, '')

But the result is longer than 4000 chars (varchar(max)), so I tried to replace this with text to support longer strings. But then I get an error data type TEXT used in VALUE method is invalid

How can I solve this problem?

Pr0no
  • 3,910
  • 21
  • 74
  • 121
  • 3
    `varchar(max)` can contain a lot more than just 4000 characters (and `text` has been deprecated for ages) –  Oct 15 '18 at 09:58
  • 1
    `varchar` can be declared up for a `varchar(8000)` before you need to use `MAX` (`nvarchar` is 4000). – Thom A Oct 15 '18 at 09:59
  • 1
    Whatever problem you're having with string truncation (and there may be one, related to how Management Studio or your client process the result) the use of `varchar(max)` isn't the cause of it, and `text` is not the solution. – Jeroen Mostert Oct 15 '18 at 10:00
  • 1
    ON a different note, `STUF` is more than happy to accept a value longer than 4000, or even 8000. Try: `SELECT LEN(STUFF(CONVERT(varchar(MAX),REPLICATE('A',8000)) + CONVERT(varchar(MAX),REPLICATE('B',8000)),1,1,''));` The returned value is 15999, so `STUFF` was quite happily able to return more than 8000 characters (note that `REPLICATE` cannot return more than 8000 characters, and hence why the query is written the way it is). – Thom A Oct 15 '18 at 10:01
  • 2
    [Using varchar(MAX) vs TEXT on SQL Server](https://stackoverflow.com/questions/834788/using-varcharmax-vs-text-on-sql-server) – Marta B Oct 15 '18 at 10:03
  • 2
    In case the comment it isn't clear, `varchar(MAX)` can contain up to 2 billion characters. No need to use text. – Dan Guzman Oct 15 '18 at 10:05
  • I don't understand -- I actually have exactly that in my query (`varchar(max)`) but when I do a `len()` on the output, I find many records truncated at 8000 chars – Pr0no Oct 15 '18 at 10:15
  • 1
    Datatypes Text, NText and Image have been deprecated since SQL Server 2008. Today, 10 years and 5 versions later (not counting Azure) I would expect them to die already, but unfortunately Microsoft still keeps them around for some reason. – Zohar Peled Oct 15 '18 at 10:16
  • `'(./text())[1]'` -> `'.'` and show your code with `len()` – Ivan Starostin Oct 15 '18 at 10:30
  • Stuff supports well over 8000 chars, [as demonstrated here.](https://rextester.com/ERCQO67515) – Zohar Peled Oct 15 '18 at 10:43
  • Check out Martin Smith's [answer](https://dba.stackexchange.com/questions/18483/varcharmax-field-cutting-off-data-after-8000-characters) to a similar question over at dba.StackExchange.Com. This might be the key to why your strings are truncated. – Zohar Peled Oct 15 '18 at 10:44
  • 1
    @Larnu replicate is more than happy to return a value longer than 8000 chars, as long as it's input string is [n]varchar(max) - try `DECLARE @T varchar(max) = 'a' SELECT LEN(REPLICATE(@T, 12345))` – Zohar Peled Oct 15 '18 at 14:56
  • @ZoharPeled you're right. Must had misread the caveat. – Thom A Oct 15 '18 at 15:17

0 Answers0