Convert and Cast for Date and Money format.

SELECT REPLACE(REPLACE(@str, CHAR(13), ''), CHAR(10), '')

The below script removes the TAB(Horozontal Tab), Line feed(New line), Carriage Return Characters in a variable @String

SET NOCOUNT ON 
DECLARE @String VARCHAR(100) 
DECLARE @CorrectedString VARCHAR(100) 
SELECT @String = 'AB    C D' 
PRINT @String 
SELECT @CorrectedString = REPLACE(@String, CHAR(9),'') 
PRINT @CorrectedString 
SELECT @CorrectedString = REPLACE(@CorrectedString, CHAR(10),'') 
PRINT @CorrectedString 
SELECT @CorrectedString = REPLACE(@CorrectedString, CHAR(13),'') 
PRINT @CorrectedString

 

Extended script which also provides length of the string:

SET NOCOUNT ON 
DECLARE @String VARCHAR(100) 
DECLARE @CorrectedString VARCHAR(100) 
SELECT @String = 'AB    C D' 
PRINT @String 
PRINT 'LENGTH='+CAST(LEN(@String) AS VARCHAR(5)) 
SELECT @CorrectedString = REPLACE(@String, CHAR(9),'') 
PRINT @CorrectedString PRINT 'LENGTH='+CAST(LEN(@CorrectedString) AS VARCHAR(5)) 
SELECT @CorrectedString = REPLACE(@CorrectedString, CHAR(10),'') 
PRINT @CorrectedString PRINT 'LENGTH='+CAST(LEN(@CorrectedString) AS VARCHAR(5)) 
SELECT @CorrectedString = REPLACE(@CorrectedString, CHAR(13),'') 
PRINT @CorrectedString PRINT 'LENGTH='+CAST(LEN(@CorrectedString) AS VARCHAR(5))

I work with a lot of databases (specifically, T-SQL databases) which collect form submissions in which there is a pretty large comment field.
Being that this is a textarea, often people make liberal use of line breaks. Unfortunately, this wreaks havoc when you try to either copy the
results from the query into Excel, or export to a CSV and then import to Excel. Even when you force double quotes around each column,
Excel still happily creates a new row whenever it sees a line break.The solution I found was to modify the SELECT query to
remove the two character entities representing line breaks and new lines in T-SQL,
which are CHAR(13) and CHAR(10). This should cover your bases for the new line characters CR, LF, and CR+LF.

The specific SQL looks like this:

 

SELECT REPLACE(REPLACE(@str, CHAR(13), ' '), CHAR(10), ' ')

select CONVERT(VARCHAR(10) ,getdate(),103) result
union
select CONVERT(VARCHAR(20) ,cast(555666.1258 as money),1);

result
18/10/2014
555,666.13
select isnull(null,'')
select replace(cast(666555.234666 as money),'.',',') --666555,23
select replace(cast(666555.235666 as money),'.',',') --666555,24

http://msdn.microsoft.com/en-us/library/ms187928(v=sql.105).aspx

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