在SQL Server 2008中修改文本字符串。

I have a table in a SQL Server 2008 database. This table has a nvarchar(256) column called 'Name'. Unfortunately, the values in this field have extra spaces included. For instance the name 'Bill' is actually stored as 'Bill ' in the table.

我在SQL Server 2008数据库中有一个表。此表有一个名为“Name”的nvarchar(256)列。不幸的是，该字段中的值包含了额外的空格。例如，“Bill”的名称实际上存储为“Bill”。

I want to update all of the records in this table to remove the extra spaces. However, I was surprised to learn that SQL does not have a TRIM function.

我想更新该表中的所有记录，以删除多余的空格。然而，我惊讶地发现SQL没有一个TRIM函数。

How do I update all of the records at once to remove the extra spaces?

如何一次更新所有记录以删除多余的空格?

Thank you!

谢谢你！

7 个解决方案

#1

You do have an RTRIM and an LTRIM function. You can combine them to get the trim function you want.

你有一个RTRIM和LTRIM函数。你可以组合它们来得到你想要的修剪功能。

UPDATE Table
SET Name = RTRIM(LTRIM(Name))

#2

You can use the RTrim function to trim all whitespace from the right. Use LTrim to trim all whitespace from the left. For example

您可以使用RTrim函数从右边修剪所有空白。使用LTrim从左对齐所有空格。例如

UPDATE Table SET Name = RTrim(Name)

Or for both left and right trim

或者左右两边都可以

UPDATE Table SET Name = LTrim(RTrim(Name))

#3

SQL Server does not have a TRIM function, but rather it has two. One each for specifically trimming spaces from the "front" of a string (LTRIM) and one for trimming spaces from the "end" of a string (RTRIM).

SQL Server没有修饰函数，而是有两个。一个用于从字符串的“前端”(LTRIM)专门修剪空间，另一个用于从字符串的“端”(RTRIM)修剪空间。

Something like the following will update every record in your table, trimming all extraneous space (either at the front or the end) of a varchar/nvarchar field:

以下内容将更新表中的每条记录，修剪varchar/nvarchar字段的所有外部空间(无论是在前面还是在末尾):

UPDATE 
   [YourTableName]
SET
   [YourFieldName] = LTRIM(RTRIM([YourFieldName]))

(Strangely, SSIS (Sql Server Integration Services) does have a single TRIM function!)

(奇怪的是，SSIS (Sql Server Integration Services)只有一个TRIM函数!)

#4

I know this is an old question but I just found a solution which creates a user defined function using LTRIM and RTRIM. It does not handle double spaces in the middle of a string.

我知道这是一个老问题，但我刚刚找到了一个解决方案，使用LTRIM和RTRIM创建一个用户定义的函数。它不处理字符串中间的双空格。

The solution is however straight forward:

然而，解决方法是直截了当的:

User Defined Trim Function

用户定义的装饰功能

#5

No Answer is true

没有答案是正确的

The true Answer is Edit Column to NVARCHAR and you found Automatically trim Execute but this code UPDATE Table SET Name = RTRIM(LTRIM(Name)) use it only with Nvarchar if use it with CHAR or NCHAR it will not work

真正的答案是编辑列到NVARCHAR，您发现自动修剪执行，但是这个代码更新表集名称= RTRIM(LTRIM(Name))只在NVARCHAR上使用它，如果它与CHAR或NCHAR一起使用，它将不起作用

#6

This function trims a string from left and right. Also it removes carriage returns from the string, an action which is not done by the LTRIM and RTRIM

这个函数将字符串从左到右进行修剪。它还从字符串中删除回车，这是一个不是由LTRIM和RTRIM完成的操作

IF OBJECT_ID(N'dbo.TRIM', N'FN') IS NOT NULL
    DROP FUNCTION dbo.TRIM;
GO
CREATE FUNCTION dbo.TRIM (@STR NVARCHAR(MAX)) RETURNS NVARCHAR(MAX)
BEGIN
    RETURN(LTRIM(RTRIM(REPLACE(REPLACE(@STR ,CHAR(10),''),CHAR(13),''))))
END;
GO

#7

I would try something like this for a Trim function that takes into account all white-space characters defined by the Unicode Standard (LTRIM and RTRIM do not even trim new-line characters!):

对于一个考虑到Unicode标准定义的所有空格字符(LTRIM和RTRIM甚至不包括新行字符)的Trim函数，我将尝试类似的方法:

IF OBJECT_ID(N'dbo.IsWhiteSpace', N'FN') IS NOT NULL
    DROP FUNCTION dbo.IsWhiteSpace;
GO

-- Determines whether a single character is white-space or not (according to the UNICODE standard).
CREATE FUNCTION dbo.IsWhiteSpace(@c NCHAR(1)) RETURNS BIT
BEGIN
	IF (@c IS NULL) RETURN NULL;
	DECLARE @WHITESPACE NCHAR(31);
	SELECT @WHITESPACE = ' ' + NCHAR(13) + NCHAR(10) + NCHAR(9) + NCHAR(11) + NCHAR(12) + NCHAR(133) + NCHAR(160) + NCHAR(5760) + NCHAR(8192) + NCHAR(8193) + NCHAR(8194) + NCHAR(8195) + NCHAR(8196) + NCHAR(8197) + NCHAR(8198) + NCHAR(8199) + NCHAR(8200) + NCHAR(8201) + NCHAR(8202) + NCHAR(8232) + NCHAR(8233) + NCHAR(8239) + NCHAR(8287) + NCHAR(12288) + NCHAR(6158) + NCHAR(8203) + NCHAR(8204) + NCHAR(8205) + NCHAR(8288) + NCHAR(65279);
	IF (CHARINDEX(@c, @WHITESPACE) = 0) RETURN 0;
	RETURN 1;
END
GO

IF OBJECT_ID(N'dbo.Trim', N'FN') IS NOT NULL
    DROP FUNCTION dbo.Trim;
GO

-- Removes all leading and tailing white-space characters. NULL is converted to an empty string.
CREATE FUNCTION dbo.Trim(@TEXT NVARCHAR(MAX)) RETURNS NVARCHAR(MAX)
BEGIN
	-- Check tiny strings (NULL, 0 or 1 chars)
	IF @TEXT IS NULL RETURN N'';
	DECLARE @TEXTLENGTH INT = LEN(@TEXT);
	IF @TEXTLENGTH < 2 BEGIN
		IF (@TEXTLENGTH = 0) RETURN @TEXT;
		IF (dbo.IsWhiteSpace(SUBSTRING(@TEXT, 1, 1)) = 1) RETURN '';
		RETURN @TEXT;
	END
	-- Check whether we have to LTRIM/RTRIM
	DECLARE @SKIPSTART INT;
	SELECT @SKIPSTART = dbo.IsWhiteSpace(SUBSTRING(@TEXT, 1, 1));
	DECLARE @SKIPEND INT;
	SELECT @SKIPEND = dbo.IsWhiteSpace(SUBSTRING(@TEXT, @TEXTLENGTH, 1));
	DECLARE @INDEX INT;
	IF (@SKIPSTART = 1) BEGIN
		IF (@SKIPEND = 1) BEGIN
			-- FULLTRIM
			-- Determine start white-space length
			SELECT @INDEX = 2;
			WHILE (@INDEX < @TEXTLENGTH) BEGIN -- Hint: The last character is already checked
				-- Stop loop if no white-space
				IF (dbo.IsWhiteSpace(SUBSTRING(@TEXT, @INDEX, 1)) = 0) BREAK;
				-- Otherwise assign index as @SKIPSTART
				SELECT @SKIPSTART = @INDEX;
				-- Increase character index
				SELECT @INDEX = (@INDEX + 1);
			END
			-- Return '' if the whole string is white-space
			IF (@SKIPSTART = (@TEXTLENGTH - 1)) RETURN ''; 
			-- Determine end white-space length
			SELECT @INDEX = (@TEXTLENGTH - 1);
			WHILE (@INDEX > 1) BEGIN 
				-- Stop loop if no white-space
				IF (dbo.IsWhiteSpace(SUBSTRING(@TEXT, @INDEX, 1)) = 0) BREAK;
				-- Otherwise increase @SKIPEND
				SELECT @SKIPEND = (@SKIPEND + 1);
				-- Decrease character index
				SELECT @INDEX = (@INDEX - 1);
			END
			-- Return trimmed string
			RETURN SUBSTRING(@TEXT, @SKIPSTART + 1, @TEXTLENGTH - @SKIPSTART - @SKIPEND);
		END 
		-- LTRIM
		-- Determine start white-space length
		SELECT @INDEX = 2;
		WHILE (@INDEX < @TEXTLENGTH) BEGIN -- Hint: The last character is already checked
			-- Stop loop if no white-space
			IF (dbo.IsWhiteSpace(SUBSTRING(@TEXT, @INDEX, 1)) = 0) BREAK;
			-- Otherwise assign index as @SKIPSTART
			SELECT @SKIPSTART = @INDEX;
			-- Increase character index
			SELECT @INDEX = (@INDEX + 1);
		END
		-- Return trimmed string
		RETURN SUBSTRING(@TEXT, @SKIPSTART + 1, @TEXTLENGTH - @SKIPSTART);
	END ELSE BEGIN
		-- RTRIM
		IF (@SKIPEND = 1) BEGIN
			-- Determine end white-space length
			SELECT @INDEX = (@TEXTLENGTH - 1);
			WHILE (@INDEX > 1) BEGIN 
				-- Stop loop if no white-space
				IF (dbo.IsWhiteSpace(SUBSTRING(@TEXT, @INDEX, 1)) = 0) BREAK;
				-- Otherwise increase @SKIPEND
				SELECT @SKIPEND = (@SKIPEND + 1);
				-- Decrease character index
				SELECT @INDEX = (@INDEX - 1);
			END
			-- Return trimmed string
			RETURN SUBSTRING(@TEXT, 1, @TEXTLENGTH - @SKIPEND);
		END 
	END
	-- NO TRIM
	RETURN @TEXT;
END
GO

#1