Menu
We've a table with a varchar2(100) column, that occasionally contains carriage-return & line-feeds. We should like to remove those characters in the SQL query. We're using:
REPLACE( col_name, CHR(10) ) which has no effect, however replacing 'CHR(10)' for a more conventional 'letter' character proves that the REPLACE function works otherwise. We have also found that
REPLACE( col_name, CHR(10), '_' ) finds the location of the new-line, but inserts the underscore after it, rather than replacing it.
Running on Oracle8i. Upgrading is not an option.
801
For carriage return ascii value is 13. Use CHR(13) for carraige return.
In the Find box hold down the Alt key and type 0 1 0 for the line feed and Alt 0 1 3 for the carriage return. They can now be replaced with whatever you want.
The Oracle/PLSQL REGEXP_REPLACE function is an extension of the REPLACE function. This function, introduced in Oracle 10g, will allow you to replace a sequence of characters in a string with another set of characters using regular expression pattern matching.
Another way is to use TRANSLATE:
TRANSLATE (col_name, 'x'||CHR(10)||CHR(13), 'x') The 'x' is any character that you don't want translated to null, because TRANSLATE doesn't work right if the 3rd parameter is null.
197
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
