How to trim a string in SQL Server before 2017?

Question

SELECT LTRIM(RTRIM(Names)) AS Names FROM Customer

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

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

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

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]