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.

18 comments:

nit said...

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

DBA said...

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

Admin said...

Its really works
.
.
Tnx dude

SenthyL said...

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

Naseeruddin said...

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

:-)

Cheers
Naseer

Unknown said...

Fantastic !

NarendraMuthum said...
This comment has been removed by the author.
NarendraMuthum said...

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

someone said...

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

Dave said...

thanks! very helpful

Anonymous said...

thanks!! it fixes my problem . . . .

Rajesh said...

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

StrageMe said...

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

StrageMe said...

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

Unknown said...

Thank you... really helpful

RK said...

Hi Sairam,

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

Regards,
RK

Unknown said...

Appreciate the post. Sorted me out.

Unknown said...

It works great.