我有具有以下值的 sql 表：'test1'、'test2'。我需要洗掉字串中的所有空格。
看起來很簡單，但 TRIM、LTRIM、RTRIM 或 REPLACE(column,' ','') 不起作用。
LEN() 函式將該空格計為一個字符。值 'test1' 的長度為 6。
我可以通過哪種方式選擇沒有空格的列？我需要值“test1”。
uj5u.com熱心網友回復：
的最小重復的例子，不設定。
請嘗試以下解決方案。
SQL
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 (' FL ', ' Miami')
, (' FL ', ' Fort Lauderdale ')
, (' NY ', ' New York ')
, (' NY ', '')
, (' NY ', NULL);
-- DDL and sample data population, end
-- before
SELECT * FROM @mockTbl;
-- remove invisible 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;
uj5u.com熱心網友回復：
正如在評論中發現的那樣，值末尾的字符不是空格，而是回車。LTRIM并且RTRIM不要洗掉這些字符，TRIM默認情況下只洗掉空格（字符 32）。
如果要洗掉其他一些字符，可以使用TRIM，但您需要使用TRIM({Characters} FROM {String})語法告訴它洗掉所述其他字符。下面洗掉前導和尾隨空格 ( ' ')、回車CHAR(13)符 ( CHAR(10))和換行符 ( )：
CREATE TABLE dbo.YourTable (SomeString varchar(50));
GO
INSERT INTO dbo.YourTable (SomeString)
VALUES('Trailing Space'),
('Trailing Line Break' CHAR(10)),
('Trailing CRLF' CHAR(13) CHAR(10)),
('Trailing CRLF and spaces ' CHAR(13) CHAR(10) ' ');
GO
DECLARE @TrimCharacters varchar(10) = ' ' CHAR(13) CHAR(10);
SELECT SomeString,
LEN(SomeString) AS Len,
DATALENGTH(SomeString) AS DataLength,
TRIM(SomeString) AS Trimmed,
DATALENGTH(TRIM(SomeString)) AS TrimmedDataLength,
TRIM(@TrimCharacters FROm SomeString) AS WellTrimmed,
DATALENGTH(TRIM(@TrimCharacters FROm SomeString)) AS WellTrimmedDataLength
FROM dbo.YourTable;
GO
DROP TABLE dbo.YourTable;
uj5u.com熱心網友回復：
您可以使用下面的函式來檢查字串的每個字符是否在 ASCII 值 32 到 126（數字和字母表）內。這將洗掉不在 ASCII 范圍內的“字符”。
查詢陳述句：
DECLARE @TestString varchar(10) = 'test1 '
DECLARE @Result nvarchar(max)
SET @Result = ''
DECLARE @character nvarchar(1)
DECLARE @characterposition int
SET @characterposition = 1
WHILE @characterposition <= LEN(@TestString)
BEGIN
SET @character = SUBSTRING(@TestString , @characterposition, 1)
IF ASCII(@character) >= 32 AND ASCII(@character) <= 126
SET @Result = @Result @character
SET @characterposition = @characterposition 1
END
SELECT @Result
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/347759.html
標籤：sql sql-server