提问人：Yang L 提问时间：8/1/2023 更新时间：8/12/2023 访问量：112
sqlcmd，单行乘积多行
sqlcmd, single row product multiple lines
问：
我正在使用sqlcmd命令将SQL Server表导出到CSV文件。
sqlcmd -S EC2AMAZ-5UNBD90 -d miadmfggp_live -Q "SELECT SDESC FROM dbo.pmdocs WHERE HMY=95613900;" -s "," -o "C:\pmdocs_ms_v1.csv" -E -y0
我注意到这里有一个问题。表中的某些行可能会在 CSV 文件中生成两行。我注意到它总是发生在 SDESC 字段上。例如，如果列值为 ，则 CSV 文件可能如下所示："Tier 547 door is broken Tier 543 does not lock latch broken 5883_1179695_0.jpg"
Tier 547 door is broken
Tier 543 does not lock latch broken 5883_1179695_0.jpg
我想要的是表中每一行的 CSV 行。请告知如何解决该问题。
答：
0赞
Yitzhak Khabinsky
8/1/2023
#1
请尝试以下解决方案。它将删除空格，即所有不可见的 TAB、回车符和换行符。
SQL #1
UDF 及其测试工具。udf_tokenize()
USE tempdb;
GO
DROP FUNCTION IF EXISTS dbo.udf_tokenize;
GO
/*
1. All invisible TAB, Carriage Return, and Line Feed characters will be replaced with spaces.
2. Then leading and trailing spaces are removed from the value.
3. Further, contiguous occurrences of more than one space will be replaced with a single space.
*/
CREATE FUNCTION dbo.udf_tokenize(@input VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
RETURN (SELECT CAST('<r><![CDATA[' + @input + ' ' + ']]></r>' AS XML).value('(/r/text())[1] cast as xs:token?','VARCHAR(MAX)'));
END
GO
-- DDL and sample data population, start
DECLARE @mockTbl TABLE (ID INT IDENTITY(1,1), col_1 VARCHAR(100), col_2 VARCHAR(100));
INSERT INTO @mockTbl (col_1, col_2) VALUES
(CHAR(13) + ' FL ' + CHAR(9), CHAR(13) + CHAR(10) + ' Miami'),
(' FL ', ' Fort Lauderdale '),
(' NY ', ' New York '),
(' NY ', ''),
(' NY ', NULL);
-- DDL and sample data population, end
-- before
SELECT *, LEN(col_2) AS [col_2_len]
FROM @mockTbl;
-- remove invisible white space chars
UPDATE @mockTbl
SET col_1 = dbo.udf_tokenize(col_1)
, col_2 = dbo.udf_tokenize(col_2);
-- after
SELECT *, LEN(col_2) AS [col_2_len]
FROM @mockTbl;
SQL#2
因此，您的 SQL 语句将如下所示：
SELECT dbo.udf_tokenize(SDESC) FROM dbo.pmdocs WHERE HMY=95613900;
0赞
Martin Smith
8/12/2023
#2
你可以做
SELECT REPLACE(REPLACE(REPLACE(SDESC, char(13)+ char(10), ' '), char(10), ' '), char(13), ' ') AS SDESC
FROM dbo.pmdocs WHERE HMY=95613900
用空格替换任何 CRLF、CR 或 LF。
评论
sqlcmd