SQL Server DataType of TEXT 不保持格式 [英] SQL Server DataType of TEXT does not maintain formatting

问题描述

我必须在 SQL Server 2005 中使用数据类型为 TEXT 的列来存储我需要使用换行符和制表符进行格式化的字符串.

I have to use a column with a datatype of TEXT in SQL Server 2005 to store a string that I need formatted with newlines and tab character for example.

在调用存储过程之前，我添加了一个断点来查看我在 VS2010 中的文本可视化器中发送的文本，并且所有格式都正是我想要的样子，但是当我转到数据库，并复制出数据，格式丢失.从我所看到的，每当字符串中出现换行符或制表符时，文本数据类型只是用空格替换它.对于换行符，我使用的是 vbNewline，对于制表符，我使用的是 vbTab 或 Xml.Formatting.Indented

prior to calling the stored proc, I added a breakpoint to view the text I'm sending in the text visualizer in VS2010, and all formatting is exactly how I want it to be, but when I go the the cell in the database, and copy out the data, the formatting is lost. From what I can see, every time it a newline character or tab character is present in the string, the text datatype just replaces it with a space. For newlines I'm using vbNewline and for tabs I'm using vbTab or Xml.Formatting.Indented

值得注意的是，我不能更改列的数据类型，因为它只是一个我必须用于特定目的的表.

It's worth note that I can NOT change the data type of the column, because it's simply a table i have to use for a specific purpose.

关于在数据库中使用 at 数据类型的文本正确设置格式的任何建议?

Any suggestions on getting this to format correctly in the database with at datatype of text?

示例:

表示表的SQL表脚本:

SQL Table Script Representing the table:

CREATE TABLE [dbo].[FooLog](
    [FooID] [int] IDENTITY(1,1) NOT NULL,
    [FooInfo] [text] NULL )

字符串生成器代码:

Dim sb As New StringBuilder()

sb.AppendFormat("{0}{1}", "Some text", vbNewLine)
sb.AppendLine("Some foo bar data")
sb.AppendLine(String.Format("{0}data{1}{0}some more data", vbTab, vbNewLine))

Dim stringToStoreInFooInfo As String = sb.ToString()

存储在数据库中:

db.AddInParameter(cmd, "FooInfo", SqlDbType.Text, stringToStoreInFooInfo)

我现在没有用代码读回它...我正在使用 Ctrl-C 或右键单击复制"将它从单元格中复制出来

I'm not reading it back with code at the moment... I'm copying it out of the cell with Ctrl-C or right-click "copy"

推荐答案

它确实很好地保留了格式，因为复制出 SSMS 单元格会丢失格式.

It does preserve the formatting fine, you're losing the format by copying out of the SSMS cell.

一旦你有代码将文本拉回来，你应该仍然有格式.

Once you've got code pulling the text back out you should still have the formatting in place.

例如，如果您在 SSMS 中运行以下命令，您将丢失换行符.我认为它会为其他空白字符切换空格.

For example if you run the following in SSMS, you'll lose the newlines. It switches in spaces for other whitespace chars I think.

Select
'
This
    is
some fomatted
text
'

这篇关于SQL Server DataType of TEXT 不保持格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！