Question: I
have table rows that contain carriage return characters (\n)and my
SQL*Plus output gets messed up. I need to know the appropriate
change all command to remove a string from all rows in my table.
How can I run a “change all” command to remove all occurrences of
these special characters?
Answer: If you know C or C++, the
'\n' is the carriage return character. SQL*PLus read this, and
inserts a carriage return. The "\n" is equivalent to chr(10),
and you can insert this into a Oracle string:
-- insert a
carriage return between columns
select
col1||chr(10)||col2
from mytab;
In your case, you need to update all of your rows to
remove the carriage returns. . . .
I would use the
regexp_replace function to remove the |\n" from the rows:
REGEXP_REPLACE is this syntax:
regexp_replace( source,
pattern, replace string, position, occurrence, options)
The
source can be a string literal, variable, or column. The
pattern is the expression to be replaced. The replace string
is the text that will replace the matching patterns. The
optional position defines the location to begin searching the source
string and it defaults to 1. The optional occurrence defines
the occurrence of the pattern that you want replaced. This
defaults to 0 (all occurrences).
Something like this might
work. I have not tested this; you may need to
"escape" the special character backslash.
-- create a test table with
all strings in all rows changed
set escape ‘\’
create table
mytab1
as
select mycol =
regexp_replace(my_column, '\n', '')
from mytab;
Once you
get this working, you can make a change all string update statement.
In this example, we
escape the backslash by following it with a percent sign:
-- remove all special
characters in a table, escaping the special character
update
mytab1
set mycol = mycol
= regexp_replace(my_column, '\%n', '')
from mytab;
|
|
 |
|
Guarantee your Success!
Oracle is the
world's most complex, robust and flexible database, considered
impossible to master without a mentor.
That's why all BC
Oracle trainers are working professionals, experts in Oracle who
share their tips and secrets. |
|
| |
|
Burleson is the American Team
Note:
This Oracle
documentation was created as a support and Oracle training reference for use by our
DBA performance tuning consulting professionals.
Feel free to ask questions on our
Oracle forum.
Verify
experience!
Anyone
considering using the services of an Oracle support expert should
independently investigate their credentials and experience, and not rely on
advertisements and self-proclaimed expertise. All legitimate Oracle experts
publish
their Oracle
qualifications.
Errata?
Oracle technology is changing and we
strive to update our BC Oracle support information. If you find an error
or have a suggestion for improving our content, we would appreciate your
feedback. Just
e-mail:
 and include the URL for the page.
Copyright ? 1996 - 2012
All rights reserved.
Oracle ?
is the registered trademark of Oracle Corporation.
|
|
