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

I need to output the contents of a text field using MS Query Analyzer. I have tried this:

我需要使用MS Query Analyzer输出文本字段的内容。我试过这个：

select top 1 text from myTable

(where text is a text field)

（其中text是文本字段）

and

和

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

The first one prints only the first 2000 or so characters and the second only prints the first 8000 characters. Is there any way to get all of the text?

第一个只打印前2000个左右的字符，第二个只打印前8000个字符。有没有办法得到所有的文字？

Notes:

笔记：

must work with SQL Server 7
必须使用SQL Server 7

3 个解决方案

#1

I don't think you can use varchar(MAX) in MSSQL7, so here's something that will give you all the data (note, what I'm understanding is you just want to visually see the data, and you aren't going put it in a variable or return it).

我不认为你可以在MSSQL7中使用varchar（MAX），所以这里有一些可以提供所有数据的东西（注意，我理解的是你只是想在视觉上看到数据，而你不会放它在变量中或返回它）。

So, this will print off the entire string so you can visually see what's in the field:

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

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

#2

I haven't used Query Analyzer in a while, however you can adjust the maximum amount of characters displayed in the results window in the Options window. See the MSDN documentation.

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

#3

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

Use this stored proc. THe only down side is you get a line break every 8000 charachters :(

使用此存储过程。每个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*/

This was originally posted on SQLServerCentral.com at http://www.sqlservercentral.com/scripts/Print/63240/

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

#1