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.
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.
How do I update all of the records at once to remove the extra spaces?
Thank you!
This question is related to
sql-server
sql-server-2008
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
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
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.
The solution is however straight forward:
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
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).
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:
UPDATE
[YourTableName]
SET
[YourFieldName] = LTRIM(RTRIM([YourFieldName]))
(Strangely, SSIS (Sql Server Integration Services) does have a single TRIM function!)
You can use the RTrim function to trim all whitespace from the right. Use LTrim to trim all whitespace from the left. For example
UPDATE Table SET Name = RTrim(Name)
Or for both left and right trim
UPDATE Table SET Name = LTrim(RTrim(Name))
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!):
IF OBJECT_ID(N'dbo.IsWhiteSpace', N'FN') IS NOT NULL_x000D_
DROP FUNCTION dbo.IsWhiteSpace;_x000D_
GO_x000D_
_x000D_
-- Determines whether a single character is white-space or not (according to the UNICODE standard)._x000D_
CREATE FUNCTION dbo.IsWhiteSpace(@c NCHAR(1)) RETURNS BIT_x000D_
BEGIN_x000D_
IF (@c IS NULL) RETURN NULL;_x000D_
DECLARE @WHITESPACE NCHAR(31);_x000D_
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);_x000D_
IF (CHARINDEX(@c, @WHITESPACE) = 0) RETURN 0;_x000D_
RETURN 1;_x000D_
END_x000D_
GO_x000D_
_x000D_
IF OBJECT_ID(N'dbo.Trim', N'FN') IS NOT NULL_x000D_
DROP FUNCTION dbo.Trim;_x000D_
GO_x000D_
_x000D_
-- Removes all leading and tailing white-space characters. NULL is converted to an empty string._x000D_
CREATE FUNCTION dbo.Trim(@TEXT NVARCHAR(MAX)) RETURNS NVARCHAR(MAX)_x000D_
BEGIN_x000D_
-- Check tiny strings (NULL, 0 or 1 chars)_x000D_
IF @TEXT IS NULL RETURN N'';_x000D_
DECLARE @TEXTLENGTH INT = LEN(@TEXT);_x000D_
IF @TEXTLENGTH < 2 BEGIN_x000D_
IF (@TEXTLENGTH = 0) RETURN @TEXT;_x000D_
IF (dbo.IsWhiteSpace(SUBSTRING(@TEXT, 1, 1)) = 1) RETURN '';_x000D_
RETURN @TEXT;_x000D_
END_x000D_
-- Check whether we have to LTRIM/RTRIM_x000D_
DECLARE @SKIPSTART INT;_x000D_
SELECT @SKIPSTART = dbo.IsWhiteSpace(SUBSTRING(@TEXT, 1, 1));_x000D_
DECLARE @SKIPEND INT;_x000D_
SELECT @SKIPEND = dbo.IsWhiteSpace(SUBSTRING(@TEXT, @TEXTLENGTH, 1));_x000D_
DECLARE @INDEX INT;_x000D_
IF (@SKIPSTART = 1) BEGIN_x000D_
IF (@SKIPEND = 1) BEGIN_x000D_
-- FULLTRIM_x000D_
-- Determine start white-space length_x000D_
SELECT @INDEX = 2;_x000D_
WHILE (@INDEX < @TEXTLENGTH) BEGIN -- Hint: The last character is already checked_x000D_
-- Stop loop if no white-space_x000D_
IF (dbo.IsWhiteSpace(SUBSTRING(@TEXT, @INDEX, 1)) = 0) BREAK;_x000D_
-- Otherwise assign index as @SKIPSTART_x000D_
SELECT @SKIPSTART = @INDEX;_x000D_
-- Increase character index_x000D_
SELECT @INDEX = (@INDEX + 1);_x000D_
END_x000D_
-- Return '' if the whole string is white-space_x000D_
IF (@SKIPSTART = (@TEXTLENGTH - 1)) RETURN ''; _x000D_
-- Determine end white-space length_x000D_
SELECT @INDEX = (@TEXTLENGTH - 1);_x000D_
WHILE (@INDEX > 1) BEGIN _x000D_
-- Stop loop if no white-space_x000D_
IF (dbo.IsWhiteSpace(SUBSTRING(@TEXT, @INDEX, 1)) = 0) BREAK;_x000D_
-- Otherwise increase @SKIPEND_x000D_
SELECT @SKIPEND = (@SKIPEND + 1);_x000D_
-- Decrease character index_x000D_
SELECT @INDEX = (@INDEX - 1);_x000D_
END_x000D_
-- Return trimmed string_x000D_
RETURN SUBSTRING(@TEXT, @SKIPSTART + 1, @TEXTLENGTH - @SKIPSTART - @SKIPEND);_x000D_
END _x000D_
-- LTRIM_x000D_
-- Determine start white-space length_x000D_
SELECT @INDEX = 2;_x000D_
WHILE (@INDEX < @TEXTLENGTH) BEGIN -- Hint: The last character is already checked_x000D_
-- Stop loop if no white-space_x000D_
IF (dbo.IsWhiteSpace(SUBSTRING(@TEXT, @INDEX, 1)) = 0) BREAK;_x000D_
-- Otherwise assign index as @SKIPSTART_x000D_
SELECT @SKIPSTART = @INDEX;_x000D_
-- Increase character index_x000D_
SELECT @INDEX = (@INDEX + 1);_x000D_
END_x000D_
-- Return trimmed string_x000D_
RETURN SUBSTRING(@TEXT, @SKIPSTART + 1, @TEXTLENGTH - @SKIPSTART);_x000D_
END ELSE BEGIN_x000D_
-- RTRIM_x000D_
IF (@SKIPEND = 1) BEGIN_x000D_
-- Determine end white-space length_x000D_
SELECT @INDEX = (@TEXTLENGTH - 1);_x000D_
WHILE (@INDEX > 1) BEGIN _x000D_
-- Stop loop if no white-space_x000D_
IF (dbo.IsWhiteSpace(SUBSTRING(@TEXT, @INDEX, 1)) = 0) BREAK;_x000D_
-- Otherwise increase @SKIPEND_x000D_
SELECT @SKIPEND = (@SKIPEND + 1);_x000D_
-- Decrease character index_x000D_
SELECT @INDEX = (@INDEX - 1);_x000D_
END_x000D_
-- Return trimmed string_x000D_
RETURN SUBSTRING(@TEXT, 1, @TEXTLENGTH - @SKIPEND);_x000D_
END _x000D_
END_x000D_
-- NO TRIM_x000D_
RETURN @TEXT;_x000D_
END_x000D_
GOSource: Stackoverflow.com