Replace function query

Question

One column of my query output contains char 13 ( new line character). To replace it with nothing I am trying to use below function but it is giving me below error ORA-00936: missing expression

select
replace(AUDITOR_COMMENTS,char(13),'')  
from csa_sli_all.T_CONV_QUOTE

When I put char(13) in quote 'char(13)' error goes but it will not do as desired. I think I cannot include char(13) in quotes . I am using Oracle Database 10g Release 10.2.0.1.0 - 64bit Production

score 6 · Answer 1 · answered May 14 '12 at 06:29

6

The function isn't char it's chr try calling:

select
replace(AUDITOR_COMMENTS,chr(13),'')  
from csa_sli_all.T_CONV_QUOTE

answered May 14 '12 at 06:29

sebastian

2,427
4
32
37

score 1 · Answer 2 · answered May 14 '12 at 06:29

1

try chr(13) instead of char(13) and see if it works

answered May 14 '12 at 06:29

Satya

8,693
5
34
55

shareef · Answer 3 · 2012-05-14T06:44:07.167

1

replace(your_data, chr(13), '')

try this as @sebastian said

select
replace(AUDITOR_COMMENTS,chr(13),'')  
from csa_sli_all.T_CONV_QUOTE

edited May 14 '12 at 06:44

answered May 14 '12 at 06:32

shareef

9,255
13
58
89

http://stackoverflow.com/questions/2268860/trim-whitespaces-new-line-and-tab-space-in-a-string-in-oracle and http://stackoverflow.com/questions/407027/pl-sql-replace-function-isnt-handling-carriage-returns-line-feeds – shareef May 14 '12 at 06:37

score 1 · Answer 4 · answered May 14 '12 at 06:37

1

Try this :

REPLACE(col_name, CHR(13) + CHR(10), '')

or

REPLACE(REPLACE( col_name, CHR(10) ), CHR(13) )

answered May 14 '12 at 06:37

Jeevi

2,962
6
39
60

Hi... Carriage returns = 13 ; Line Feeds = 10 ; I just thought replacing the both would work.. and fortunately, it worked.. :) – Jeevi May 14 '12 at 07:31

Replace function query

4 Answers4