Updating nvarchar(max) in SQL Server with huge value

Question

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.

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?

Why are you doing it manually anyway, shouldn't you be using paramterized queries from your application? Also an empty string '' isn't valid JSON. — Charlieface
– Charlieface, Commented Sep 28, 2023 at 19:26

Martin Smith · Accepted Answer · 2023-09-28 19:03:14Z

1

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

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.

edited Sep 28, 2023 at 19:03

answered Sep 28, 2023 at 18:26

Martin Smith

457k97 gold badges777 silver badges886 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Filip5991 Over a year ago

Excellent, I didn't realize this was just an intellisense thing, silly of me not to try it anyway. Thanks a lot for the detailed answer!

Ben Thul · Accepted Answer · 2023-09-28 20:01:07Z

1

This is a limitation of SSMS, not SQL. That is, after a certain string length (which one could find empirically if one were so inclined), syntax highlighting gives up but it's still valid SQL. I tested this with the ff code:

declare @j varchar(max) = '{"Text": "testtesttest..."}';
select ISJSON(@j), DATALENGTH(@j);

Using increasingly large values for the test data (I used 'test' * 16000 | set-clipboard in powershell).

answered Sep 28, 2023 at 20:01

Ben Thul

32.9k4 gold badges52 silver badges74 bronze badges

2 Comments

Filip5991 Over a year ago

Should've just tried to execute it then...Thanks a lot for your time and help :-)

Ben Thul Over a year ago

I get it... the highlighting goes away and it feels broken. Chalk up my insight to having started my career on a green screen.

Collectives™ on Stack Overflow

Updating nvarchar(max) in SQL Server with huge value

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related