Question: I have a table where all rows
contain a string that I need to change. For example, please
assume that I have to change "north" to south" in all of my table
rows. How do I use SQL to perform a "change all" operation
against the entire table?
Answer: There are several ways to perform
a change all for all strings in all rows of a table. Prior to
regular expressions, you performed a change all using using the
substr and instr functions, but today you can use the
regexp_replace function to quickly change all rows in a
table.
For complete examples of doing a change all in Oracle SQL, see
the recommended book
Advanced Oracle SQL Programming.
Here is how to use the
regexp_replace function to change all string in all of your
table rows. Also see how to
change
all special characters from table rows
REGEXP_REPLACE is
this syntax:
regexp_replace( source, pattern, replace string,
position, occurrence, options)
Something like this will create a test table with all changed
values:.
-- change all characters in
a table
create
table
mytab1
as
select mycol =
regexp_replace(my_column, 'north', 'south')
from mytab;
Once you get this working, you can make a change all string
update statement. In this example, we issue a SQL update
to change a string in all table rows:
-- update to change all
strings in a table
update
mytab1
set mycol =
mycol = regexp_replace(my_column, 'north', 'south')
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.
|
