blocks|key|1539209|text|实际上，SQL命令或脚本字符串中的新行可以是CR、LF或CR%2BLF中的任何一行。要获得所有这些内容，您需要这样的内容：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1539210|SELECT+REPLACE(REPLACE(@str,+CHAR(13),+''),+CHAR(10),+'')|code-block|syntax|javascript|1539211|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Actually a new line in a SQL command or script string can be any of CR, LF or CR+LF. To get them all, you need something like this:

<pre><code>SELECT REPLACE(REPLACE(@str, CHAR(13), ''), CHAR(10), '')
</code></pre>

blocks|key|1323868|text|REPLACE(@string,+CHAR(13)+%2B+CHAR(10),+'')|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1323869|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>REPLACE(@string, CHAR(13) + CHAR(10), '')
</code></pre>

blocks|key|1539142|text|T-SQL中的换行符由CHAR(13)和CHAR(10)+(回车符%2B换行符)表示。因此，您可以使用要用来替换换行符的文本来创建一条REPLACE语句。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1539143|REPLACE(MyField,+CHAR(13)+%2B+CHAR(10),+'something+else')|code-block|syntax|javascript|1539144|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

The Newline in T-SQL is represented by CHAR(13) &amp; CHAR(10) (Carriage return + Line Feed). Accordingly, you can create a REPLACE statement with the text you want to replace the newline with.

<pre><code>REPLACE(MyField, CHAR(13) + CHAR(10), 'something else')
</code></pre>

blocks|key|1539274|text|要做大多数人想做的事情，可以创建一个不是真正的换行字符的占位符。然后，您实际上可以将这些方法组合在一起：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1539275|REPLACE(REPLACE(REPLACE(MyField,+CHAR(13)+%2B+CHAR(10),+'something+else'),+CHAR(13),+'something+else'),+CHAR(10),+'something+else')|code-block|syntax|javascript|1539276|这种方法只需替换一次。以下方法：|1539277|REPLACE(REPLACE(MyField,+CHAR(13),+''),+CHAR(10),+'')|1539278|如果您只想去掉CRLF字符，则非常有用，但如果您想要一个占位符，例如|1539279| |1539280|或者别的什么，那么第一种方法会更准确一点。|1539281|entityMap^0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|U|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|W|8|@]|9|@]|A|$]]|$1|M|3|N|5|D|7|X|8|@]|9|@]|A|$E|F]]|$1|O|3|P|5|6|7|Y|8|@]|9|@]|A|$]]|$1|Q|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]]|R|$]]

To do what most people would want, create a placeholder that isn't an actual line breaking character. Then you can actually combine the approaches for:
<pre><code>REPLACE(REPLACE(REPLACE(MyField, CHAR(13) + CHAR(10), 'something else'), CHAR(13), 'something else'), CHAR(10), 'something else')
</code></pre>
This way you replace only once. The approach of:
<pre><code>REPLACE(REPLACE(MyField, CHAR(13), ''), CHAR(10), '')
</code></pre>
Works great if you just want to get rid of the CRLF characters, but if you want a placeholder, such as
<pre><code>&lt;br/&gt;
</code></pre>
or something, then the first approach is a little more accurate.

blocks|key|1324358|text|有时|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1324359|REPLACE(myString,+CHAR(13)+%2B+CHAR(10),+'+')|code-block|syntax|javascript|1324360|行不通的。在这种情况下，请使用以下代码片段：|1324361|REPLACE(REPLACE(myString,+CHAR(13),''),+CHAR(10),+'+')|1324362|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

Sometimes

<pre><code>REPLACE(myString, CHAR(13) + CHAR(10), ' ')
</code></pre>

won't work. In that case use the following snippet code:

<pre><code>REPLACE(REPLACE(myString, CHAR(13),''), CHAR(10), ' ')
</code></pre>

blocks|key|1539359|text|如果您有使用sp_helptext打开过程，则只需复制新的sql查询中的所有文本，然后按ctrl%2Bh按钮，使用正则表达式替换，并将%5E\n放入查找字段中，替换为空。有关更多详细信息，请查看图像。enter+image+description+here|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1539360|entityMap|0|LINK|mutability|MUTABLE|url|https://i.stack.imgur.com/my5fz.png^0|2O|S|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@$A|M|B|N|1|O]]|C|$]]|$1|D|3|-4|5|6|7|P|8|@]|9|@]|C|$]]]|E|$F|$5|G|H|I|C|$J|K]]]]

If you have have open procedure with using sp_helptext then just copy all text in new sql query and press ctrl+h button use regular expression to replace and put ^\n in find field replace with blank .
for more detail check image.<a href="https://i.stack.imgur.com/my5fz.png" rel="nofollow noreferrer">enter image description here</a>

blocks|key|1324230|text|@Cerebrus解决方案:不支持字符串的for+H2+"%2B“。所以：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1324231|REPLACE(string,+CHAR(13)+%7C%7C+CHAR(10),+'replacementString')|code-block|syntax|javascript|1324232|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

To @Cerebrus solution: for H2 for strings "+" is not supported. So:

<pre><code>REPLACE(string, CHAR(13) || CHAR(10), 'replacementString')
</code></pre>

blocks|key|1324294|text|上面发布的答案/之前报道的替换CHAR(13)CHAR(10)回车符的答案：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1324295|REPLACE(REPLACE(REPLACE(MyField,+CHAR(13)+%2B+CHAR(10),+'something+else'),+CHAR(13),+'something+else'),+CHAR(10),+'something+else')|code-block|syntax|javascript|1324296|将永远不会到达代码的REPLACE(MyField,+CHAR(13)+%2B+CHAR(10),+'something+else')部分，并将返回不需要的结果：|offset|length|style|CODE|1324297|'something+else''something+else'|1324298|而不是单项的预期结果：|1324299|'something+else'|1324300|这将需要将替换脚本重写为：|1324301|REPLACE(REPLACE(REPLACE(MyField,+CHAR(10),+'something+else'),+CHAR(13),+'something+else'),+CHAR(13)+%2B+CHAR(10),+'something+else')|1324302|由于流程首先测试第一个/最左边的REPLACE语句，然后在失败时将继续测试下一个REPLACE语句。|1324303|entityMap^0|0|0|A|1J|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|10|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|11|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|12|8|@$I|13|J|14|K|L]]|9|@]|A|$]]|$1|M|3|N|5|D|7|15|8|@]|9|@]|A|$E|F]]|$1|O|3|P|5|6|7|16|8|@]|9|@]|A|$]]|$1|Q|3|R|5|D|7|17|8|@]|9|@]|A|$E|F]]|$1|S|3|T|5|6|7|18|8|@]|9|@]|A|$]]|$1|U|3|V|5|D|7|19|8|@]|9|@]|A|$E|F]]|$1|W|3|X|5|6|7|1A|8|@]|9|@]|A|$]]|$1|Y|3|-4|5|6|7|1B|8|@]|9|@]|A|$]]]|Z|$]]

The answer posted above/earlier that was reported to replace CHAR(13)CHAR(10) carriage return:

<pre><code>REPLACE(REPLACE(REPLACE(MyField, CHAR(13) + CHAR(10), 'something else'), CHAR(13), 'something else'), CHAR(10), 'something else')
</code></pre>

Will never get to the <code>REPLACE(MyField, CHAR(13) + CHAR(10), 'something else')</code> portion of the code and will return the unwanted result of:

<pre><code>'something else''something else'
</code></pre>

And NOT the desired result of a single:

<pre><code>'something else'
</code></pre>

That would require the REPLACE script to be rewritten as such:

<pre><code>REPLACE(REPLACE(REPLACE(MyField, CHAR(10), 'something else'), CHAR(13), 'something else'), CHAR(13) + CHAR(10), 'something else')
</code></pre>

As the flow first tests the 1st/Furthest Left REPLACE statement, then upon failure will continue to test the next REPLACE statement.

blocks|key|1324405|text|我想要清理列的内容以生成csv文件，所以我想去掉varchar中的逗号(，)以及换行符和carrage+return。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1324406|我还想最终使用生成的csv来创建另一个脚本(将行插入到另一个数据库中)，因此还需要将“在varchar内”更改为“”，因此以以下代码结束...|1324407|REPLACE(REPLACE(REPLACE(REPLACE(ErrorMessage,+CHAR(13),+''),+CHAR(10),+''),',',''),'''','''''')|code-block|syntax|javascript|1324408|也许还有其他更好的方法，但它完成了工作。|1324409|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|N|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|O|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

I was wanting to sanitize the contents of a column to generate a csv file, so want to get rid of the comma (,) within the varchar as well as newline and carrage-return.
I also wanted to eventually use the generated csv to create another script (to insert rows into another db) so also needed to change ' within the varchar to '' so ended up with this...
<pre><code>REPLACE(REPLACE(REPLACE(REPLACE(ErrorMessage, CHAR(13), ''), CHAR(10), ''),',',''),'''','''''')
</code></pre>
There may be other nicer ways but it got the job done.

I would like to replace (or remove) a newline character in a TSQL-string. 
Any Ideas?

The obvious 

<pre><code>REPLACE(@string, CHAR(13), '')
</code></pre>

just won't do it...

Replace a newline in TSQL

数据库

我想替换(或删除)TSQL字符串中的换行符。有什么想法吗？显而易见的REPLACE(@string, CHAR(13), '')就是不会这么做。

问在TSQL中替换换行符
EN

回答 9

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在TSQL中替换换行符EN

回答 9

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在TSQL中替换换行符
EN