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

Convert and Cast for Date and Money format.