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:

Nithi 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

kamlendra pratap said...

Its really works
.
.
Tnx dude

Kumaru 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

kamakshi 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

mr-obu said...

thanks!! it fixes my problem . . . .

Rajesh said...

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

Jiaa said...

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

Jiaa said...

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

Manogna Allu said...

Thank you... really helpful

RK said...

Hi Sairam,

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

Regards,
RK

Aaron Jevons said...

Appreciate the post. Sorted me out.

Pulla Rao said...

It works great.