Handling White space in T-sql is very annoying. When you are comparing strings it is crucial that you remove any white space or special characters that could interfere with your results. You can user rtrim(ltrim () but the problem is rtrim(ltrim () does not catch special characters such as Hexadecimal X09.
Since we are not able to see the whitespaces it creates a challenge when we implement our queries so please use this t-sql to create a function or a stored procedure to remove any whitespace and special characters from your T-SQL strings.
select ltrim(rtrim( 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(REPLACE( ' Enter The string you want to sanitize here' ,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(18),'') ,char(20),'') ,char(21),'') ,char(22),'') ,char(23),'') ,char(24),'') ,char(25),'') ,char(26),'') ,char(27),'') ,char(28),'') ,char(29),'') ,char(30),'') ,char(32),'') ,char(32),'') ))
Char () T-sql
http://msdn.microsoft.com/en-us/library/ms187323.aspx