jsonsql-serversql-updatenvarchar

Updating nvarchar(max) in SQL Server with huge value


I'm trying to manually update a SQL Server column of type nvarchar(max) with a huge JSON parsed as text.

It's a simple, straightforward query:

UPDATE Books 
SET Story = '' 
WHERE Id = 23

The problem happens when the JSON I try to insert into Story contains a huge value, part of the JSON (I think the breaking point was around 38k characters)

"Text": "testtesttesttest..." (imagine the value is 50k characters)

Usually text values in single quotes in SQL Server Management Studio are red, this one isn't because of the huge value. If I break the text into multiple new lines, then it is accepted as a 'valid' string, but the JSON validation fails -

JSON text is not properly formatted. Unexpected character ' ' is found at position 33043.

enter image description here

I also tried splitting the big line using + CHAR(13) +', but did not succeed.

Is there a way to update the value while maintaining the whole JSON?


Solution

  • Lines of longer than 32,768 charactes (POWER(2,15)) break intellisense and syntax highlighting for that line and all subsequent lines.

    You can just ignore this as it will still execute fine.

    Alternatively you can break the lines up and concatenate the string. This won't embed line breaks into the string.

    Make sure all elements you concatenate have the N prefix and the first one is typed as MAX

    UPDATE Books 
        SET Story = CAST(N'YourLongString' AS NVARCHAR(MAX)) + 
                    N'YourLongStringContinued'
    WHERE Id = 23
    

    Or you can use the backslash character to continue the string onto a new line without inserting a line break but you need to resist the urge to indent the string here to avoid inserting unwanted white space into the string contents itself (and make certain no trailing space exists after the \ as it must be the last character on the line).

        UPDATE Books 
            SET Story = 
    N'YourLongString\
    YourLongStringContinued'
        WHERE Id = 23
    

    enter image description here

    If you are happy to accept that intellisense will be broken for the long line but are just trying to avoid it being broken in subsequent lines too you could even declare a variable for this and assign the long string in the final line of the file. And use GOTO to get the variable assigned and to jump back to the original place in the file.