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

}
at

24 comments:

  1. jakrpanda2:03 PM

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

    ReplyDelete
  2. Oggelito5:35 AM

    Thanks, was looking for exactly this.

    ReplyDelete
  3. ME20079:57 AM

    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.

    ReplyDelete
  4. Unknown7:35 AM

    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!

    ReplyDelete
  5. Jon Mitchell11:20 AM

    Perfect!

    ReplyDelete
  6. Jon Mitchell11:27 AM

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

    ReplyDelete
  7. Unknown12:16 PM

    thank you for your code.

    ReplyDelete
  8. Bharath Palanivelu4:22 AM

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

    ReplyDelete
  9. niemenen10:45 PM

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

    ReplyDelete
  10. Darkstar6:16 PM

    Pure Briliance!

    ReplyDelete
  11. Unknown3:55 AM

    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)

    ReplyDelete
  12. musclPete2:05 PM

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

    ReplyDelete
  13. Anonymous9:57 AM

    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

    ReplyDelete
  14. Jon3:36 PM

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

    Thx,

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

    ReplyDelete
  15. alex_uk1:51 AM

    Found this helpful. Thanks you.

    ReplyDelete
  16. Chad2:33 AM

    That was exactly what I have been after.

    Thank you !

    ReplyDelete
  17. Brent8:14 PM

    Thanks!

    ReplyDelete
  18. raj9:16 AM

    TQ very much

    ReplyDelete
  19. Chad11:25 AM

    Thanks!

    ReplyDelete
  20. Deepak Giri12:28 AM

    Thanks, was looking for exactly this! :)

    ReplyDelete
  21. ramakrishna5:30 AM

    thanks u very much this exactly im looking for..

    ReplyDelete
  22. Anonymous6:27 AM

    Life saver, thanks!!

    ReplyDelete
  23. GuitarSam79:01 AM

    Superb!! Thank yoU!!!

    ReplyDelete
  24. Unknown11:40 PM

    This helped me out quickly, thnx!

    ReplyDelete