CODEWITHSUNDEEP
sqloracle-databaseoracle10g
MAS1
MAS1

Reputation: 1701

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

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.

Upvotes: 2

Views: 2688

Answers (2)

schurik
schurik

Reputation: 7928

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

Upvotes: 6

Jon Heller
Jon Heller

Reputation: 36798

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.

Upvotes: 1

Related Questions