Trimming text strings in SQL Server 2008

Question

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

User · Answer

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!)

User · Answer

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

User · Answer

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      x000D   x000D  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  lt  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  lt   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  gt  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  lt   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  gt  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  GO x000D   x000D   x000D

User · Answer

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

User · Answer

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

User · Answer

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   User Defined Trim Function

User · Answer

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

[sql-server] Trimming text strings in SQL Server 2008

Examples related to sql-server

Examples related to sql-server-2008