CODEWITHSUNDEEP
plsqloracle10g
zeesu
zeesu

Reputation: 57

Write a custom oracle function to remove chr(10)=linefeed & chr(13)=carriage return and trim column and return the value

Is it possible to write a custom oracle function that does the following

remove char(10)
remove char(13)
trim

so if i write

select custom_function(col1) from tab1 it returns value with linefeed,carriage return and trimmed

If possible i also want to replace a long balnk line between two words with single blank.

where col1 is string

Can someone please help me in this

Thanks a lot

Upvotes: 0

Views: 1727

Answers (1)

William Robertson
William Robertson

Reputation: 15991

For the trimming question, you probably just need rtrim, e.g:

select rtrim(somecolumn, chr(10)||chr(13)||' ')
from   ( select 'Some text

                ' as somecolumn
         from dual );

For replacing multiple whitespace characters you can use regexp_replace, e.g:

select regexp_replace(somecolumn, '\s{2,}', ' ')
from   ( select 'Some   text' as somecolumn
         from dual );

Both together:

select regexp_replace(rtrim(somecolumn, chr(10)||chr(13)||' '), '\s{2,}', ' ')
from   ( select 'Some   text

                ' as somecolumn
         from dual );

Upvotes: 1

Related Questions