Trimming text strings in 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.
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!
You do have an RTRIM and an LTRIM function. You can combine them to get the trim function you want.
UPDATE Table
SET Name = RTRIM(LTRIM(Name))
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))
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!)
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
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