时间戳

首页 > 解决方案 > 从 SQL 中的连接尾记录中删除回车和换行？

sql-server - 从 SQL 中的连接尾记录中删除回车和换行？

问题描述

我有一个在 SSMS 中运行的 SQL 查询生成的数据集，其中包括一个 1 行尾记录，该记录以 .txt 格式导出并保存到记事本。但是，预告片记录自动包含十六进制控件，以在作为预告片记录的连接字段（750 个字符）之后包含换行/回车记录。在导出为 .txt 格式之前，我怎样才能从预告片的末尾消除它？文件不得包含任何空行。

我已经在我的预告片记录的 SELECT 语句中尝试了以下行代码，这似乎是这种情况的常见修复：

REPLACE(REPLACE('T'+CAST(RIGHT(REPLACE(STR(COUNT(*)),' ','0'),9) AS VARCHAR)+SPACE(740),CHAR(10),''),CHAR(13),'')

但是，它在以 .txt 格式导出时仍然包含换行符和回车十六进制字符。

REPLACE(REPLACE('T'+CAST(RIGHT(REPLACE(STR(COUNT(*)),' ','0'),9) AS VARCHAR)+SPACE(740),CHAR(10),''),CHAR(13),'')

预期结果是文件不包含导出文档的包含的十六进制视图中显示的 0D 和 0A 字节字符。

导出的 .TXT 文件的十六进制视图

标签： sql-servertsqlreplacenotepad

解决方案

在创建文本文件时，我无法从您所说的（以及以下评论）中判断问题是在 SQL 级别还是在 SQL 级别之外。无论哪种方式，您都可以使用NGrams8K来解决这个问题（该链接还包括一个 VARCHAR(MAX) 版本，它比 8K 版本慢，但仍然非常快。）

我经常通过OPENROWSET（或其他方式）导入构建脚本，修改文本然后将结果写入替换旧文件的新文件（使用BCP），从而自动处理手动更新构建脚本。下面是一些代码，可帮助您了解如何使用 NGrams 函数来解决此问题。

分析：

DECLARE @someString VARCHAR(8000) =
'blah blah blah.... ;
blah blah     .... ;
blah blah blah.... ;
 ...;';

SELECT
  ng.position,
  ng.token,
  charValue   = ASCII(ng.Token),
  binaryValue = CAST(ng.token AS VARBINARY(2))
FROM samd.NGrams8K(@someString,1) AS ng;

返回（为简洁起见截断）：

position             token     charValue   binaryValue
-------------------- --------- ----------- -----------
1                    b         98          0x62
2                    l         108         0x6C
3                    a         97          0x61
4                    h         104         0x68
5                              32          0x20
6                    b         98          0x62
...
...
68                   .         46          0x2E
69                   .         46          0x2E
70                   .         46          0x2E
71                             32          0x20
72                   ;         59          0x3B
73                             13          0x0D
74                             10          0x0A
75                             32          0x20
76                             32          0x20
....

注意第 73 和 74 行？这些是您要删除的两个字符： CHAR(13) & CHAR(10) AKA 0D和0A。你想删除那些。

使用 NGrams 或 NGrams8k，您可以通过定位最后一个 CHAR(13) 来获得最后一个 LF+CR 的位置。

DECLARE @someString VARCHAR(8000) =
'blah blah blah.... ;
blah blah     .... ;
blah blah blah.... ;
 ...;';

SELECT MAX(ng.position)
FROM   samd.NGrams8K(@someString,1) AS ng
WHERE  ASCII(ng.Token) = 13;

回报： 73

请注意，我使用变量 (@someString) 进行演示，如果 GUI 添加最终的 LF/CR，那么您必须导入该文件并将内容分配给变量。

DECLARE @someString VARCHAR(8000) =
'blah blah blah.... ;
blah blah     .... ;
blah blah blah.... ;
 ...;';

-- Use STUFF to remove the last CHAR(13)+CHAR(10)
DECLARE @newString VARCHAR(8000) = 
STUFF(
  @someString,
  (
    SELECT MAX(ng.position)
    FROM   samd.Ngrams8K(@someString,1) AS ng
    WHERE  ASCII(ng.Token) = 13
  ),2,'');

此代码 ^^^^ 删除最终的 LF/CR。

更新：

我刚刚看到大卫的回应；如果是这种情况，您可以使用我的解决方案将文件拉入，更改内容并编写一个新文件。下面是我如何做到这一点的一个例子（不完美，但它有效）。

CREATE PROC dbo.FileTransform_clean
  @sourceFile NVARCHAR(500),
  @destFile   NVARCHAR(500),
  @badText    NVARCHAR(1000),
  @cleanup    BIT = 1
AS
BEGIN
  -- 0. Prep
  BEGIN
    SET NOCOUNT ON;
    SET @sourceFile = TRIM(@sourceFile);

    DECLARE @pos  SMALLINT       = CHARINDEX('\',REVERSE(@sourceFile));
    DECLARE @path NVARCHAR(4000) = SUBSTRING(@sourceFile,1,LEN(@sourceFile)-@pos),
            @file NVARCHAR(4000) = SUBSTRING(@sourceFile,LEN(@sourceFile)-@pos+2,4000);
    DECLARE @t TABLE (subdirectory NVARCHAR(4000), depth TINYINT, [file] BIT);

    INSERT @t(subdirectory, depth, [file])
      EXEC [master].dbo.xp_DirTree @path,1,1;

    IF NOT EXISTS (SELECT 1 FROM @t AS t WHERE t.subdirectory = @file)
    BEGIN
      DECLARE @error VARCHAR(100) = 
        'The source file, '+ISNULL(@sourceFile,'NULL')+' was not found.';
      PRINT @error;
      GOTO error
    END

    IF OBJECT_ID('tempdb..##import','U') IS NOT NULL DROP TABLE ##import;
    CREATE TABLE ##import(Document VARCHAR(MAX));
  END

  -- 1. File Import
  BEGIN
    PRINT 'Performing file import...';

    DECLARE @SQL NVARCHAR(4000) = 'INSERT INTO ##import(Document)
    SELECT * FROM OPENROWSET (BULK N'''+@sourceFile+''', SINGLE_BLOB) AS Document;';

    EXEC (@SQL);
  END

  -- 2. Transformation
  BEGIN
    PRINT 'Performing file transform...';

    DECLARE @query NVARCHAR(4000) = 
      N'SELECT STRING_AGG(s.item,CHAR(10)) WITHIN GROUP (ORDER BY s.ItemNumber)
        FROM   SQLToolbox_Misc.samd.delimitedSplitAB((SELECT i.Document FROM ##import AS i),CHAR(10)) AS s
        WHERE NOT EXISTS (SELECT 1 FROM STRING_SPLIT('''+@badText+''','','') AS ss 
                  WHERE CHARINDEX(ss.[value],s.item)>0);'

    SET @SQL = 'bcp '+'"'+@query+'" '+'queryout "'+@destFile+'" -c -T -S '+@@SERVERNAME;
    SET @SQL = REPLACE(@sql,CHAR(13)+CHAR(10),'');

    DECLARE @SQLText VARCHAR(8000) = '  Executing:'+CHAR(10)+'  '+@SQL;
    PRINT @SQLText;

    EXEC [master]..xp_cmdshell @SQL;
    IF @cleanup = 1 DROP TABLE ##import;
  END

  error:
END

这段代码做了一些完全不同的事情，但你可以注意我是如何： 1. 使用 OPENROWSET 拉入文件 2. 对内容做一些事情（在我的代码中，我删除了@badText 3. 定义的任何“错误文本”使用 BCP 的新文件

希望这可以帮助。

推荐阅读