在 SQL Server 中打印文本字段内容的最简单方法

Question

我需要使用 MS 查询分析器输出文本字段的内容。 我已经尝试过：（

select top 1 text from myTable

其中 text 是 text 字段）

并且

DECLARE @data VarChar(8000) 
select top 1 @data = text from myTable
PRINT @data

第一个仅打印前 2000 个左右字符，第二个仅打印前 8000 个字符。 有什么办法可以获取全部文本吗？

注意：

• 必须与 SQL Server 7 配合使用

Answer 1

我不认为你可以在 MSSQL7 中使用 varchar(MAX) ，所以这里有一些可以为你提供所有数据的东西（注意，我的理解是你只是想直观地看到数据，并且你不会把它在一个变量中或返回它）。

因此，这将打印整个字符串，以便您可以直观地看到该字段中的内容：

DECLARE @limit as int,
        @charLen as int,
        @current as int,
        @chars as varchar(8000)

SET @limit = 8000

SELECT  TOP 1 @charLen = LEN(text)
FROM    myTable

SET @current = 1

WHILE @current < @charLen
BEGIN
    SELECT  TOP 1 @chars = SUBSTRING(text,@current,@limit)
    FROM    myTable
    PRINT @chars

    SET @current = @current + @limit
END

Answer 2

我已经有一段时间没有使用查询分析器了，但是您可以在“选项”窗口中调整结果窗口中显示的最大字符数。 请参阅 MSDN 文档。

Answer 3

http://shortfastcode.blogspot.com/2011 /10/getting-around-sql-server-print-8000.html

使用此存储过程。 唯一的缺点是每 8000 个字符就有一个换行符 :(

CREATE PROCEDURE [dbo].[LongPrint]
      @String NVARCHAR(MAX)

AS

/*
Example:

exec LongPrint @string =
'This String
Exists to test
the system.'

*/

/* This procedure is designed to overcome the limitation
in the SQL print command that causes it to truncate strings
longer than 8000 characters (4000 for nvarchar).

It will print the text passed to it in substrings smaller than 4000
characters.  If there are carriage returns (CRs) or new lines (NLs in the text),
it will break up the substrings at the carriage returns and the
printed version will exactly reflect the string passed.

If there are insufficient line breaks in the text, it will
print it out in blocks of 4000 characters with an extra carriage
return at that point.

If it is passed a null value, it will do virtually nothing.

NOTE: This is substantially slower than a simple print, so should only be used
when actually needed.
 */

DECLARE
               @CurrentEnd BIGINT, /* track the length of the next substring */
               @offset tinyint /*tracks the amount of offset needed */

set @string = replace(  replace(@string, char(13) + char(10), char(10))   , char(13), char(10))

WHILE LEN(@String) > 1
BEGIN

IF CHARINDEX(CHAR(10), @String) between 1 AND 4000
    BEGIN

SET @CurrentEnd =  CHARINDEX(char(10), @String) -1
           set @offset = 2
    END
    ELSE
    BEGIN
           SET @CurrentEnd = 4000
            set @offset = 1
    END

PRINT SUBSTRING(@String, 1, @CurrentEnd)

set @string = SUBSTRING(@String, @CurrentEnd+@offset, 1073741822)

END /*End While loop*/

这最初发布在 SQLServerCentral.com 上，网址为 http://www.sqlservercentral.com/scripts/Print/63240/