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), '')

}

Comments

GuitarSam7

Superb!! Thank yoU!!!

New blogger

Life saver, thanks!!

ramakrishna

thanks u very much this exactly im looking for..

Deepak Giri

Thanks, was looking for exactly this! :)

Chad Renstrom

Thanks!

raj

TQ very much

Brent

Thanks!

Chad

That was exactly what I have been after.

Thank you !

Thanks!

alex_uk

Found this helpful. Thanks you.

Safety Harbor Shaman

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

Thx,

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

Lily

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

musclPete

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

caractacus

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)

Darkstar

Pure Briliance!

Durga

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

Bharath

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

Nae

thank you for your code.

Jon

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

Jon

Perfect!

Katie

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!

ME2007

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.

Oscar "Oggelito" Aagren

Thanks, was looking for exactly this.

jakrpanda

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