OraFAQ Forum: SQL & PL/SQL » How do you remove CrLf from strings in pl/sql?

Home » SQL & PL/SQL » SQL & PL/SQL » How do you remove CrLf from strings in pl/sql? (Oracle 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How do you remove CrLf from strings in pl/sql? [message #309467]

Thu, 27 March 2008 10:25

zim327
Messages: 15
Registered: October 2007

Junior Member

Hi,
I need to remove carriage returns (CrLf x0D0A in hex) (and replace them with nothing) which are in the middle of strings

Is there a way to do this with a query or do I need to write a procedure to do this?
How do you specify a CrLf in the replace command?

Thanks,

Report message to a moderator

Re: How do you remove CrLf from strings in pl/sql? [message #309468 is a reply to message #309467]

Thu, 27 March 2008 10:40

ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany

Senior Member

CrLf can be specified with the ASCII codes, so :

Code: [Select all] [Show/ hide]

replace( v_string, chr(13) || chr(10), '')

Report message to a moderator

Re: How do you remove CrLf from strings in pl/sql? [message #309469 is a reply to message #309467]

Thu, 27 March 2008 10:54

zim327
Messages: 15
Registered: October 2007

Junior Member

Thanks for the reply,
unfortunately I tried that and this

Code: [Select all] [Show/ hide]

REPLACE(filter, CHR(13)||CHR(10), NULL)

and the carriage return is still there! even after I type commit

If anyone has any other suggestions I'm all ears...

Report message to a moderator

Re: How do you remove CrLf from strings in pl/sql? [message #309473 is a reply to message #309467]

Thu, 27 March 2008 11:08

zim327
Messages: 15
Registered: October 2007

Junior Member

Great news!
I can't believe I found the answer! Here it is:

Code: [Select all] [Show/ hide]

REPLACE(REPLACE(filter, CHR(10), ''), CHR(13), '')

Enjoy,

Report message to a moderator

Previous Topic:	execute immediate with cursor problem
Next Topic:	Passing resp_id as parameter urgent.. please help

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed May 21 06:02:55 CDT 2025