[sql] How to trim a string in SQL Server before 2017?

In SQL Server 2017, you can use this syntax, but not in earlier versions:

SELECT Name = TRIM(Name) FROM dbo.Customer;

To trim any set of characters from the beginning and end of a string, you can do the following code where @TrimPattern defines the characters to be trimmed. In this example, Space, tab, LF and CR characters are being trimmed:

Declare @Test nvarchar(50) = Concat (' ', char(9), char(13), char(10), ' ', 'TEST', ' ', char(9), char(10), char(13),' ', 'Test', ' ', char(9), ' ', char(9), char(13), ' ')

DECLARE @TrimPattern nvarchar(max) = '%[^ ' + char(9) + char(13) + char(10) +']%'

SELECT SUBSTRING(@Test, PATINDEX(@TrimPattern, @Test), LEN(@Test) - PATINDEX(@TrimPattern, @Test) - PATINDEX(@TrimPattern, LTRIM(REVERSE(@Test))) + 2)

I assume this is a one-off data scrubbing exercise. Once done, ensure you add database constraints to prevent bad data in the future e.g.

ALTER TABLE Customer ADD
   CONSTRAINT customer_names__whitespace
      CHECK (
             Names NOT LIKE ' %'
             AND Names NOT LIKE '% '
             AND Names NOT LIKE '%  %'
            );

Also consider disallowing other characters (tab, carriage return, line feed, etc) that may cause problems.

It may also be a good time to split those Names into family_name, first_name, etc :)

To Trim on the right, use:

SELECT RTRIM(Names) FROM Customer

To Trim on the left, use:

SELECT LTRIM(Names) FROM Customer

To Trim on the both sides, use:

SELECT LTRIM(RTRIM(Names)) FROM Customer

Extended version of "REPLACE":

REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(RTRIM(LTRIM(REPLACE("Put in your Field name", ' ',' '))),'''',''), CHAR(9), ''), CHAR(10), ''), CHAR(13), ''), CHAR(160), '') [CorrValue]

To trim any set of characters from the beginning and end of a string, you can do the following code where @TrimPattern defines the characters to be trimmed. In this example, Space, tab, LF and CR characters are being trimmed:

Declare @Test nvarchar(50) = Concat (' ', char(9), char(13), char(10), ' ', 'TEST', ' ', char(9), char(10), char(13),' ', 'Test', ' ', char(9), ' ', char(9), char(13), ' ')

DECLARE @TrimPattern nvarchar(max) = '%[^ ' + char(9) + char(13) + char(10) +']%'

SELECT SUBSTRING(@Test, PATINDEX(@TrimPattern, @Test), LEN(@Test) - PATINDEX(@TrimPattern, @Test) - PATINDEX(@TrimPattern, LTRIM(REVERSE(@Test))) + 2)

To Trim on the right, use:

SELECT RTRIM(Names) FROM Customer

To Trim on the left, use:

SELECT LTRIM(Names) FROM Customer

To Trim on the both sides, use:

SELECT LTRIM(RTRIM(Names)) FROM Customer

in sql server 2008 r2 with ssis expression we have the trim function .

SQL Server Integration Services (SSIS) is a component of the Microsoft SQL Server database software that can be used to perform a broad range of data migration tasks.

you can find the complete description on this link

http://msdn.microsoft.com/en-us/library/ms139947.aspx

but this function have some limitation in itself which are also mentioned by msdn on that page. but this is in sql server 2008 r2

TRIM("   New York   ") .The return result is "New York".

SELECT LTRIM(RTRIM(Replace(Replace(Replace(name,'   ',' '),CHAR(13), ' '),char(10), ' ')))
from author

I assume this is a one-off data scrubbing exercise. Once done, ensure you add database constraints to prevent bad data in the future e.g.

ALTER TABLE Customer ADD
   CONSTRAINT customer_names__whitespace
      CHECK (
             Names NOT LIKE ' %'
             AND Names NOT LIKE '% '
             AND Names NOT LIKE '%  %'
            );

Also consider disallowing other characters (tab, carriage return, line feed, etc) that may cause problems.

It may also be a good time to split those Names into family_name, first_name, etc :)

To Trim on the right, use:

SELECT RTRIM(Names) FROM Customer

To Trim on the left, use:

SELECT LTRIM(Names) FROM Customer

To Trim on the both sides, use:

SELECT LTRIM(RTRIM(Names)) FROM Customer

in sql server 2008 r2 with ssis expression we have the trim function .

SQL Server Integration Services (SSIS) is a component of the Microsoft SQL Server database software that can be used to perform a broad range of data migration tasks.

you can find the complete description on this link

http://msdn.microsoft.com/en-us/library/ms139947.aspx

but this function have some limitation in itself which are also mentioned by msdn on that page. but this is in sql server 2008 r2

TRIM("   New York   ") .The return result is "New York".

Extended version of "REPLACE":

REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(RTRIM(LTRIM(REPLACE("Put in your Field name", ' ',' '))),'''',''), CHAR(9), ''), CHAR(10), ''), CHAR(13), ''), CHAR(160), '') [CorrValue]

I assume this is a one-off data scrubbing exercise. Once done, ensure you add database constraints to prevent bad data in the future e.g.

ALTER TABLE Customer ADD
   CONSTRAINT customer_names__whitespace
      CHECK (
             Names NOT LIKE ' %'
             AND Names NOT LIKE '% '
             AND Names NOT LIKE '%  %'
            );

Also consider disallowing other characters (tab, carriage return, line feed, etc) that may cause problems.

It may also be a good time to split those Names into family_name, first_name, etc :)

To Trim on the right, use:

SELECT RTRIM(Names) FROM Customer

To Trim on the left, use:

SELECT LTRIM(Names) FROM Customer

To Trim on the both sides, use:

SELECT LTRIM(RTRIM(Names)) FROM Customer