Thursday, March 5, 2009

How to remove new line characters and Tab's in the SQL output

How to remove new line characters and Tab's in your SQL output


select comment from emp

Result (Comment column value is )

John M.
This was a
Fine piece of
work.

select REPLACE(REPLACE(REPLACE(comment, CHR(10)), CHR(13)), CHR(9)) comment from emp

Result (Comment column value is )

John M.This was a Fine piece of work.
at

18 comments:

  1. nitSeptember 26, 2009 at 7:52 PM

    This is very useful command.It help me out.Thanks

    ReplyDelete
  2. DBAOctober 19, 2009 at 10:15 PM

    Hi
    That is great for replacing. How would I search a column to find all the new line characters (made from a windows PC) first before replacing them?
    Thanks

    ReplyDelete
  3. AdminMarch 26, 2010 at 5:43 PM

    Its really works
    .
    .
    Tnx dude

    ReplyDelete
  4. SenthyLFebruary 19, 2011 at 3:21 AM

    Wonderful post :) it helped me fruitfully :) Tons of thanks

    ReplyDelete
  5. NaseeruddinMarch 2, 2011 at 9:41 AM

    Hi ...
    I must say
    Sairamgoud .This was a Fine piece of work.

    :-)

    Cheers
    Naseer

    ReplyDelete
  6. UnknownMay 25, 2011 at 12:44 AM

    Fantastic !

    ReplyDelete
  7. NarendraMuthumDecember 20, 2011 at 4:05 PM

    This comment has been removed by the author.

    ReplyDelete
  8. NarendraMuthumDecember 20, 2011 at 4:10 PM

    Thank you so much... it's really saved me a lot..
    awesome job dude..!!!

    ReplyDelete
  9. someoneJune 14, 2012 at 2:05 PM

    also can use:
    regexp_replace(comment, chr(9)||'|'||chr(10)||'|'||chr(13), '')

    ReplyDelete
  10. DaveJune 28, 2012 at 12:22 AM

    thanks! very helpful

    ReplyDelete
  11. AnonymousJune 29, 2012 at 2:17 PM

    thanks!! it fixes my problem . . . .

    ReplyDelete
  12. RajeshJuly 3, 2012 at 3:35 AM

    Cool.. it works in all my scenarios...
    Thanks dude...

    ReplyDelete
  13. StrageMeJuly 6, 2012 at 7:29 PM

    Whoa!! it worked so well for my script where i was trying hard to get this.

    ReplyDelete
  14. StrageMeJuly 6, 2012 at 7:30 PM

    Whoa!It really worked well in my case, i was trying hard to find something like this.

    ReplyDelete
  15. UnknownOctober 30, 2012 at 10:03 PM

    Thank you... really helpful

    ReplyDelete
  16. RKDecember 13, 2012 at 4:11 PM

    Hi Sairam,

    A ton of thanks. God Bless you..!!

    Regards,
    RK

    ReplyDelete
  17. UnknownMarch 12, 2013 at 2:28 AM

    Appreciate the post. Sorted me out.

    ReplyDelete
  18. UnknownMarch 22, 2013 at 11:37 PM

    It works great.

    ReplyDelete