Execute the following Microsoft SQL Server T-SQL script in SSMS Query Editor to create a table of names with control characters (white space) imbedded and clean them with REPLACE:

USE TempDB;

SELECT Name = FirstName+CHAR(17)+' '+LastName+CHAR(9)
INTO Name
FROM AdventureWorks.Person.Contact;

SELECT * FROM Name;

UPDATE Name SET Name = REPLACE(REPLACE
(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE
(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE
(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
Name,
CHAR(1),''),CHAR(2),''),CHAR(3),''),CHAR(4),''),CHAR(5),''),CHAR(6),''),
CHAR(7),''),CHAR(8),''),CHAR(9),''),CHAR(10),''),CHAR(11),''),CHAR(12),
''),CHAR(13),''),CHAR(14),''),CHAR(15),''),CHAR(16),''),CHAR(17),''),
CHAR(18),''),CHAR(19),''),CHAR(20),''),CHAR(21),''),CHAR(22),''),CHAR(23),
''),CHAR(24),''),CHAR(25),''),CHAR(26),''),CHAR(27),''),CHAR(28),''),
CHAR(29),''),CHAR(30),''),CHAR(31),'');

SELECT * FROM Name;

Related article:

Using the SQL Server Integration Services for Data Cleansing - Fuzzy Lookups and Groupings Provide Powerful Data Cleansing Capabilities