SELECT显示和PRINT打印超长的字符

SELECT显示和PRINT打印超长的字符

 

从SQL Server 2005开始，引入了varchar(max) / nvarchar(max) 数据类型，表中可不使用LOB数据类型，从而突破单列8000 / 4000字符的限制，动态SQL也可以拼接得更长；但查看这类字符的时候，在某些地方，字符长度的限制仍然存在，比如：

1. 单列：SSMS里所返回的列值；

2. 动态SQL：print命令打印字符时；

一. SSMS里所返回的列值长度限制

在SSMS (SQL Server Management Studio)的工具-选项(Tools-Options)里可设置每列最多显示的字符数，默认为上限65535个字符，如图：

注：到SQL Server Management Studio 17为止，SSMS还并不能真正显示65535个字符，经测试，正确值为43679， 这是个已知bug，一直没被修复，可简单测试如下：

if OBJECT_ID('test_print','U') is not null
    drop table test_print

create table test_print(c1 varchar(max))

insert into test_print 
values(replicate(cast('A' as varchar(max)),65536))

select c1, len(c1) from test_print
--c1 43679

如果想要查看更多，可以把这个列导出为文本文件、或者通过substring、或者转为XML格式来查看，如下：

select substring(c1,1,8000),SUBSTRING(c1,8001,16000) from test_print
select c1 from test_print for XML PATH('')

二. PRINT命令打印字符长度限制

print命令可用来打印字符，在调试存储过程或者批处理时，经常会用到，但默认情况下print有8000/4000字符的限制，如下：

declare @nsql nvarchar(max)
declare @test_sql varchar(50)

set @nsql = replicate('A',4000)
set @test_sql = replicate('B',50)

print @nsql+@test_sql

注：RAISERROR也可用来打印，参数(severity)还可控制语句块的行为，但是RAISERROR的可打印字符长度仅为2047，这包含超长时末尾的省略号(…)，所以仅可打印2044个自定义字符

declare @str varchar(max)
set @str = replicate('A',9000)
raiserror (@str,10,0)

如果想要打印更多，可通过以下几种方法来突破字符数的限制：

1. 打印为text/ntext类型，但仍有16002 字符数限制

DECLARE @info NVARCHAR(MAX) = 'A';
SET @info = REPLICATE(@info, 16000) + 'BC This is not printed';
PRINT @info;
PRINT CAST(@info AS TEXT);
PRINT CAST(@info AS NTEXT);

2. 使用XML类型，但只能用select而不是print

DECLARE @info NVARCHAR(MAX) = 'A';
SET @info = REPLICATE(@info, 16000) + 'BC This is not printed';
SELECT CAST(@info AS XML)
SELECT @info for XML PATH('')

3. 使用substring来分段截取，封装为一个SP可直接使用，推荐

if OBJECT_ID('print_long','P') is not null
    drop proc print_long
GO

create proc print_long
(
@str       nvarchar(max)
)
as
begin
    DECLARE @Counter INT
    DECLARE @TotalPrints INT

    SET @Counter = 0
    SET @TotalPrints = (LEN(@str) / 4000) + 1
    
    WHILE @Counter < @TotalPrints 
    BEGIN
        PRINT SUBSTRING(@str, (@Counter * 4000) + 1, 4000)
        SET @Counter = @Counter + 1
    END
    --PRINT LEN(@str)
end
GO

DECLARE @info NVARCHAR(MAX) = 'A';
SET @info = REPLICATE(@info, 16000) + 'BC This is not printed';
EXEC print_long @info