SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Print Long String

Quite often, I output information through PRINT command. It works well only when message is shorter than 8000 bytes. When the message is greater than 8000 bytes, extra-characters will be removed. the simplest way to overcome this is to make a loop and print 8000 characters at a time

declare @line nvarchar(max) = Replicate(cast('0123456789' as varchar(max)), 800)+'abc
'
select @line = replicate(@line, 3)
declare @pos int = 1
while @pos <= len(@line)
begin
	print substring(@line, @pos, 8000)
	select @pos = @pos + 8000
end
/*
012345678901234567890....
abc
012345678901234567890....
6789abc
012345678901234567890....
23456789abc
*/


It seems good until I look at the line 12, 14, 16 above. what I really want to see is the first line contains numbers and the second line are letters, then the third line are numbers and fourth line are letters. Here is the procedure to display them correctly

create procedure PrintString (@str varchar(max))
as
begin
	declare @line varchar(max), @StartLocation int, @Length int, @TotalLength int, @Current int
	select @StartLocation = 1, @TotalLength = datalength(@str), @Current = 1, @Length = 0
	declare @PrintLine nvarchar(max) =
'declare @pos int = 1
while @pos <= len(@line)
begin
	print substring(@line, @pos, 8000)
	select @pos = @pos + 8000
end'
	while @Current <= @TotalLength
	begin
		if(substring(@str, @Current, 2) in( char(0x0d) + char(0x0a), char(0x0a) + char(0x0d)))
		begin
			if @Length <= 0
				print ''
			else
			begin -- line
				select @line = substring(@str, @StartLocation, @Length)
				exec sp_executesql @PrintLine, N'@Line varchar(max)' , @line
			end
			select @StartLocation = @Current + 2, @Current = @Current + 2, @Length = 0
			continue;
		end
		else if (substring(@str, @Current, 1) in(char(0x0d) , char(0x0a)))
		begin
			if @Length <= 0
				print ''
			else
			begin
				select @line = substring(@str, @StartLocation, @Length)
				exec sp_executesql @PrintLine, N'@Line varchar(max)' , @line
			end
			select @StartLocation = @Current + 1, @Current = @Current + 1, @Length = 0
			continue;
		end
		select @Current = @Current + 1, @Length = @Length + 1
	end
	if(@StartLocation <= datalength(@str))
		print substring(@str, @StartLocation, datalength(@str))
end
go
declare @str nvarchar(max) = Replicate(cast('0123456789' as varchar(max)), 800)+'abc
'
select @str = replicate(@str, 3)
exec PrintString @str
/*
01234567890...
abc
01234567890...
abc
01234567890...
abc

*/

Everything looks good now.

John Huang’s Blog

John Huang, Microsoft Certified Master in SQL Server, SQL Server MVP, is an independent SQL Server consultant in Vancouver BC, Canada. He started using SQL Server for his projects in year 1994. He has architected and implemented many SQL and BI applications serving different industrial areas. He loves talking about SQL Server and discussing SQL Server technologies with others. He blogs at http://www.sqlnotes.info.

Comments

Leave a comment on the original post [www.sqlnotes.info, opens in a new window]

Loading comments...