Thursday, March 30, 2006

Remove Carriage Return, Line Feed, Tab in TSQL

{

-- remove carriage return, line feed, and tab from a field in tsql
REPLACE(REPLACE(REPLACE(MyField, CHAR(10), ''), CHAR(13), ''), CHAR(9), '')

}

24 comments:

jakrpanda said...

just wanted to say thanks for the snippet of SQL code to remove carriage return/line feeds

Oggelito said...

Thanks, was looking for exactly this.

ME2007 said...

I have a flat file with multiple lines that belong to one person account and i need to bring all lines to one line making them as one line of comma separate type data. For example as below:
DateIn" 2007/01/01 account:155555
name" john smith Age: 18
--------
DateIn" 2007/01/01 account:166665
name" josh sammy Age: 20

With this example, how can i join the 2 lines to one line then i join the final 2 lines in one line. By the way, i have been trying to work on this in my sql server 2000.

Thanks,

ME2007.

Unknown said...

Thank you so much! This is exactly what I was looking for - and so much more simple than everything else I found out there. Nice job!

Jon Mitchell said...

Perfect!

Jon Mitchell said...

Do you have any suggestions on how to strip just the trailing CR/LFs?

Unknown said...

thank you for your code.

Bharath Palanivelu said...

Thanks... This post helped me....

niemenen said...

Thanks a million.Instant code.I fixed my code in exactly 5 mts with this Info

Darkstar said...

Pure Briliance!

Unknown said...

Trim leading/trailing characters.


DECLARE @value VARCHAR(1000)
DECLARE @pat varchar(10)
SET @pat = '%[^ ' + char(09) + char(10) + ']%'

SET @value = CHAR(10) + ' ' + CHAR(10) + 'message test ' + char(10) + ' values ' + CHAR(10) + ' ' + CHAR(10) + CHAR(10) + ' ' + CHAR(10)

SELECT SUBSTRING(
@value,
PATINDEX(@pat, @value),
LEN(@value) - PATINDEX(@pat, @value) - PATINDEX(@pat, REVERSE(@value)) + 2)

musclPete said...

Excellent - googled it and in 20 seconds had a working app!!

Anonymous said...

Thanks for your help my boyfriend was having many problems with that last week so when I saw your blog I called him immediately and even he pay for it to a technician we never imagine that it was so easy. Viagra Online
Generic Viagra Buy Viagra

Jon said...

Thanks, this line of code was great - did it exactly what I needed -

Thx,

Jon Bloom
http://www.tampabaybi.com/

alex_uk said...

Found this helpful. Thanks you.

Chad said...

That was exactly what I have been after.

Thank you !

Brent said...

Thanks!

raj said...

TQ very much

Chad said...

Thanks!

Deepak Giri said...

Thanks, was looking for exactly this! :)

ramakrishna said...

thanks u very much this exactly im looking for..

Anonymous said...

Life saver, thanks!!

GuitarSam7 said...

Superb!! Thank yoU!!!

Unknown said...

This helped me out quickly, thnx!