How to write two replace functions in one select statement in Oracle

Question

I want to replace two things in one select statement. Now I can replace one character at a time. But as per my requirment, I need to replace two things in one statement.I want to replace one special character by Zero and another is this data is stored in multiple lines. I want to fetch this data in one line.

select replace(message,chr(ACII_Value),0) 
from my_table -- I can replace special character by zero using this.

select (message,chr(10),' ') 
from my_table -- I can put all data in single line using this.

Now I want to do this in one select statement.

Srini V · Accepted Answer · 2014-03-11 11:43:36Z

6

select replace( replace( message, chr(ACII_Value), 0), chr(10), ' ') 
from my_table;

edited Mar 11, 2014 at 11:43

Srini V

11.5k15 gold badges70 silver badges93 bronze badges

answered Oct 14, 2011 at 11:09

schurik

7,9582 gold badges26 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jon Heller · Accepted Answer · 2011-10-15 04:28:09Z

1

An alternative is the TRANSLATE function:

select translate(message, chr(ASCII_Value)||chr(10), '0 ') from my_table;

Although if you're only replacing two characters, I'd go with @schurik's answer since it uses a simpler, more common function.

answered Oct 15, 2011 at 4:28

Jon Heller

37.1k6 gold badges84 silver badges150 bronze badges

Collectives™ on Stack Overflow

How to write two replace functions in one select statement in Oracle

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related