替换TSQL中的换行符

9 回答

• 710

  T-SQL中的换行符由CHAR（13）和CHAR（10）（回车换行）表示 . 因此，您可以使用要替换换行符的文本创建REPLACE语句 .

  REPLACE(MyField, CHAR(13) + CHAR(10), 'something else')
  

  回复于 2024-05-02T20:48:18+08:00
• 28

  如果您遇到只想删除尾随字符的问题，可以尝试这样做：

  WHILE EXISTS
  (SELECT * FROM @ReportSet WHERE
      ASCII(right(addr_3,1)) = 10
      OR ASCII(right(addr_3,1)) = 13
      OR ASCII(right(addr_3,1)) = 32)
  BEGIN
      UPDATE @ReportSet
      SET addr_3 = LEFT(addr_3,LEN(addr_3)-1)
      WHERE 
      ASCII(right(addr_3,1)) = 10
      OR ASCII(right(addr_3,1)) = 13
      OR ASCII(right(addr_3,1)) = 32
  END
  

  这解决了我遇到的问题，其中一个过程创建了一个具有固定行数的字段，即使这些行是空的 . 为了节省我的SSRS报告中的空间，我将其剪下来 .

  回复于 2024-05-02T20:48:18+08:00
• 137

  实际上，SQL命令或脚本字符串中的新行可以是CR，LF或CR LF中的任何一行 . 为了得到它们，你需要这样的东西：

  SELECT REPLACE(REPLACE(@str, CHAR(13), ''), CHAR(10), '')
  

  回复于 2024-05-02T20:48:18+08:00
• 21

  我可能晚了一年，但我每天都在处理查询和MS-SQL，我厌倦了内置函数LTRIM（）和RTRIM（）（并且总是要一起调用它们），以及没有 grab 最后有新行的'脏'数据，所以我认为现在是实现更好的TRIM功能的时候了 . 我欢迎同行反馈！

  免责声明：这实际上是 removes （替换为单个空格）扩展形式的空白（制表符，换行符，回车符等），因此它内部需要这些额外的特殊空格字符，所以如果它们不在't occur at the head/tail, they should be replaced with a plain space. If you purposefully stored such characters in your string (say, your column of data that you'重新开始运行这个），DON 'T DO IT! Improve this function or write your own that literally just removes those characters from the endpoints of the string, not from the ' body' .

  好的，现在免责声明已经更新，这是代码 .

  -- =============================================
  -- Description: TRIMs a string 'for real' - removes standard whitespace from ends,
  -- and replaces ASCII-char's 9-13, which are tab, line-feed, vert tab,
  -- form-feed, & carriage-return (respectively), with a whitespace
  -- (and then trims that off if it's still at the beginning or end, of course).
  -- =============================================
  CREATE FUNCTION [fn_CleanAndTrim] (
         @Str nvarchar(max)
  )
  RETURNS nvarchar(max) AS
  BEGIN
         DECLARE @Result nvarchar(max)
  
         SET @Result = LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
                LTRIM(RTRIM(@Str)), CHAR(9), ' '), CHAR(10), ' '), CHAR(11), ' '), CHAR(12), ' '), CHAR(13), ' ')))
  
         RETURN @Result
  END
  

  干杯!

  另一个免责声明：你典型的Windows换行符是CR LF，所以如果你的字符串包含那些，你最终会用"double"空格替换它们 .

  UPDATE, 2016 ：一个新版本，可让您选择用您选择的 other 个字符替换这些特殊空白字符！这还包括对Windows CR LF配对的评论和解决方法，即用单个替换替换该特定字符对 .

  IF OBJECT_ID('dbo.fn_CleanAndTrim') IS NULL
      EXEC ('CREATE FUNCTION dbo.fn_CleanAndTrim () RETURNS INT AS BEGIN RETURN 0 END')
  GO
  -- =============================================
  -- Author: Nate Johnson
  -- Source: http://stackoverflow.com/posts/24068265
  -- Description: TRIMs a string 'for real' - removes standard whitespace from ends,
  -- and replaces ASCII-char's 9-13, which are tab, line-feed, vert tab, form-feed,
  -- & carriage-return (respectively), with a whitespace or specified character(s).
  -- Option "@PurgeReplaceCharsAtEnds" determines whether or not to remove extra head/tail
  -- replacement-chars from the string after doing the initial replacements.
  -- This is only truly useful if you're replacing the special-chars with something
  -- **OTHER** than a space, because plain LTRIM/RTRIM will have already removed those.
  -- =============================================
  ALTER FUNCTION dbo.[fn_CleanAndTrim] (
      @Str NVARCHAR(MAX)
      , @ReplaceTabWith NVARCHAR(5) = ' '
      , @ReplaceNewlineWith NVARCHAR(5) = ' '
      , @PurgeReplaceCharsAtEnds BIT = 1
  )
  RETURNS NVARCHAR(MAX) AS
  BEGIN
      DECLARE @Result NVARCHAR(MAX)
  
      --The main work (trim & initial replacements)
      SET @Result = LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
          LTRIM(RTRIM(@Str))  --Basic trim
          , NCHAR(9), @ReplaceTabWith), NCHAR(11), @ReplaceTabWith)   --Replace tab & vertical-tab
          , (NCHAR(13) + NCHAR(10)), @ReplaceNewlineWith) --Replace "Windows" linebreak (CR+LF)
          , NCHAR(10), @ReplaceNewlineWith), NCHAR(12), @ReplaceNewlineWith), NCHAR(13), @ReplaceNewlineWith)))   --Replace other newlines
  
      --If asked to trim replacement-char's from the ends & they're not both whitespaces
      IF (@PurgeReplaceCharsAtEnds = 1 AND NOT (@ReplaceTabWith = N' ' AND @ReplaceNewlineWith = N' '))
      BEGIN
          --Purge from head of string (beginning)
          WHILE (LEFT(@Result, DATALENGTH(@ReplaceTabWith)/2) = @ReplaceTabWith)
              SET @Result = SUBSTRING(@Result, DATALENGTH(@ReplaceTabWith)/2 + 1, DATALENGTH(@Result)/2)
  
          WHILE (LEFT(@Result, DATALENGTH(@ReplaceNewlineWith)/2) = @ReplaceNewlineWith)
              SET @Result = SUBSTRING(@Result, DATALENGTH(@ReplaceNewlineWith)/2 + 1, DATALENGTH(@Result)/2)
  
          --Purge from tail of string (end)
          WHILE (RIGHT(@Result, DATALENGTH(@ReplaceTabWith)/2) = @ReplaceTabWith)
              SET @Result = SUBSTRING(@Result, 1, DATALENGTH(@Result)/2 - DATALENGTH(@ReplaceTabWith)/2)
  
          WHILE (RIGHT(@Result, DATALENGTH(@ReplaceNewlineWith)/2) = @ReplaceNewlineWith)
              SET @Result = SUBSTRING(@Result, 1, DATALENGTH(@Result)/2 - DATALENGTH(@ReplaceNewlineWith)/2)
      END
  
      RETURN @Result
  END
  GO
  

  回复于 2024-05-02T20:48:18+08:00
• 31

  要完成大多数人想要的操作，请创建一个不是实际换行符的占位符 . 然后你可以实际结合以下方法：

  REPLACE(REPLACE(REPLACE(MyField, CHAR(13) + CHAR(10), 'something else'), CHAR(13), 'something else'), CHAR(10), 'something else')
  

  这样您只需更换一次 . 方法：

  REPLACE(REPLACE(MyField, CHAR(13), ''), CHAR(10), '')
  

  如果你只想摆脱CRLF字符，但是如果你想要一个占位符，例如
  或者其他什么，那么第一种方法就更准确了 .

  回复于 2024-05-02T20:48:18+08:00
• 1

  如果你有使用sp_helptext的开放程序，那么只需复制新sql查询中的所有文本，然后按ctrl h按钮使用正则表达式替换并将^ \ n放入查找字段替换为空白 . 更多细节检查图像 . enter image description here

  回复于 2024-05-02T20:48:18+08:00
• 3

  @Cerebrus解决方案：不支持H2 for strings“” . 所以：

  REPLACE(string, CHAR(13) || CHAR(10), 'replacementString')
  

  回复于 2024-05-02T20:48:18+08:00
• 1

  如果您的列数据类型为“ text ”，则会收到错误消息

  消息8116，级别16，状态1，行2参数数据类型文本对替换函数的参数1无效 .

  在这种情况下，您需要将文本转换为nvarchar然后替换

  SELECT REPLACE(REPLACE(cast(@str as nvarchar(max)), CHAR(13), ''), CHAR(10), '')
  

  回复于 2024-05-02T20:48:18+08:00
• 4

  REPLACE(@string, CHAR(13) + CHAR(10), '')
  

  回复于 2024-05-02T20:48:18+08:00