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;
 |
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
