将数据从 SQL Server 2012 复制到 Excel 时出现换行问题 [英] New Line Issue when copying data from SQL Server 2012 to Excel

问题描述

我最近升级到 SQL2012 并且正在使用 Management Studio.我在数据库中的一列中存储了一个 CHAR(13) + CHAR(10).

I recently upgraded to SQL2012 and am using Management Studio. One of my columns in the database has a CHAR(13) + CHAR(10) stored in it.

当我使用 SQL Server 2008 时，这将完全复制并粘贴到 Excel 中.但是，现在复制和粘贴相同的数据会在 Excel 中的数据中创建一个新行/回车.

When I was using SQL Server 2008, this would copy and paste completely fine into Excel. Now, however, copying and pasting the same data creates a new line/ carriage return in the data I have in Excel.

是否有我在 SQL2012 中遗漏的设置可以解决此问题?我不想在每个数据库选择上简单地 REPLACE(CHAR(13) + CHAR(10)) ，因为我必须从使用 SELECT * 到定义每个单独的列.

Is there a setting I missed in SQL2012 that will resolve this issue? I don't want to simply REPLACE(CHAR(13) + CHAR(10)) on every single database selection, as I would have to go from using SELECT * to defining each individual column.

推荐答案

我最好的猜测是这不是错误，而是 Sql 2012 的一个特性.;-)在其他情况下，您会很乐意保留您的 cr-lf，例如复制大量文本时.只是它在您的情况下效果不佳.

My best guess is that this is not a bug, but a feature of Sql 2012. ;-) In other contexts, you'd be happy to retain your cr-lf's, like when copying a big chunk of text. It's just that it doesn't work well in your situation.

你总是可以在你的选择中去掉它们.这将使您在两个版本中都按照您的意愿进行查询:

You could always strip them out in your select. This would make your query for as you intend in both versions:

select REPLACE(col, CHAR(13) + CHAR(10), ', ') from table

这篇关于将数据从 SQL Server 2012 复制到 Excel 时出现换行问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！