I'm using string_agg to develop a long string to be used in some dynamic sql. I'm using nvarchar(max), but it continues to truncate.
This the code I'm using. It gets me a result with a length of 1920 characters. (Note: I have noticed I'm adding an extra "or" on the end of my code here, but that seems unrelated to the truncation issue.)
select string_agg(convert(nvarchar(max),FieldName), ' is not null or ')
from UDFDefinition
However when I do the following, I am able to get the entire string with a length of 1932 characters.
if object_id(N'tempdb..#FieldNames') is not null begin drop tbale #fieldnames end;
create table #FieldNames (FieldName nvarchar(max))
insert into #FieldNames
select concat(convert(nvarchar(max),FieldName), ' is not null')
from UDFDefinitions
select string_agg(FieldName, ' or ')
from #FieldNames
drop table #FieldNames
While I have a working solution, I'd really like to understand why string_agg isn't working. The only topics I could find only suggest adding a cast to nvarchar(max).
December 23, 2022 at 10:41 pm
This was removed by the editor as SPAM
December 23, 2022 at 11:33 pm
if by not working you mean its not displaying it on SSMS that is "right" - SSMS will not display the full content.
if you wish to see all its content do a select convert(xml, string_agg(fieldname, ' or '))
you can also check the content size to see how it compares to your 1932 chars.
December 23, 2022 at 11:48 pm
elaborate on ssms limits? have a look at https://stackoverflow.com/questions/64704240/ssms-what-is-the-max-string-size-that-can-be-displayed-in-the-grid-for-varchar
and did you try what I said?
December 23, 2022 at 11:56 pm
My setting is set to over 65,000 characters.
I did try your xml method and it truncated in exactly the same place.
December 24, 2022 at 12:05 am
then something you doing not exactly as it is.
I've tried your sql just replacing your function with sys.columns from master db and it works as expected with a total of 29324 chars being displayed and this being equal to the length of the said aggregation.
and note that while you state you are getting truncated you didn't really give any good example of it, neither exactly how you are determining it failed.
code I tested below (executed on a Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64) (Build 19045: )
if object_id(N'tempdb..#FieldNames') is not null begin drop table #fieldnames end;
create table #FieldNames (FieldName nvarchar(max))
insert into #FieldNames
select concat(convert(nvarchar(max),FieldName), ' is not null')
from (select name as FieldName
from sys.columns) t
select string_agg(FieldName, ' or '), len( string_agg(FieldName, ' or '))
from #FieldNames
drop table #FieldNames
December 24, 2022 at 12:09 am
Yeah, that code does work. I know it does. My question is why that works and the simple version without a temp table doesn't work.
What do you get when you do this?
select string_agg(convert(nvarchar(max),[name]), ' is not null or ')
from sys.columns
the last " is not null or " gets removed from the output which is as per design e.g. the separator is only added if there is a string to separate, so last separator is always omitted (as there isn't anything to separate)
also very clearly stated on the manuals https://learn.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-ver16
Concatenates the values of string expressions and places separator values between them. The separator isn't added at the end of string.
December 24, 2022 at 12:23 am
Doh! As soon as I read your comment, I'm smacking my hand against my head. I think I need to go eat something and take a breather before I look at any more code today.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply