Remove White Space and Special Characters in your T-SQL strings

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



Let's share thisShare on FacebookShare on Google+Tweet about this on TwitterShare on LinkedIn