Character Replace in Oracle SQL
In my sql query I am pulling all the email address from my company; example john.smith@abc.com
We are changing our domain to john.smith@hospital.org
Want to know if there is a way for me to search for the string @abc.com and replace it with @hospital.org in my query?
We are changing our domain to john.smith@hospital.org
Want to know if there is a way for me to search for the string @abc.com and replace it with @hospital.org in my query?
johnsone
The REPLACE function should do what you need.
First: You should ask Oracle questions in the Oracle Database Topic Area. Some of us don't monitor other areas and can easily miss questions. I've changed the Topic Areas for you.
You really don't provide enough information.
Do you have stored procedures/functions/packa
Do you have a folder in some OS that has a lot of scripts that you wish to replace?
What?
If in Oracle code, you can use a query against user_source to find the code that has the domain:
select name, type from user_source where lower(text) like '%@abc.com%';
Then you can use dbms_metadata.get_ddl to extract the actual DDL, to a simple replace with your favorite editor and re-execute the DDL.
You really don't provide enough information.
Do you have stored procedures/functions/packa
Do you have a folder in some OS that has a lot of scripts that you wish to replace?
What?
If in Oracle code, you can use a query against user_source to find the code that has the domain:
select name, type from user_source where lower(text) like '%@abc.com%';
Then you can use dbms_metadata.get_ddl to extract the actual DDL, to a simple replace with your favorite editor and re-execute the DDL.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>>it would end up looking something like this:
The assumption is they are using a single email address column in a table or series of tables. The problem is more involved if there is the possibility of more than one column in a table that might contain the email address.
It also assumes @abc.com is the entire end of the field. If you are updating a column where the email address can exist in blocks of text, the above statements won't work. You will need to double-ended wildcard in my example.
The assumption is they are using a single email address column in a table or series of tables. The problem is more involved if there is the possibility of more than one column in a table that might contain the email address.
It also assumes @abc.com is the entire end of the field. If you are updating a column where the email address can exist in blocks of text, the above statements won't work. You will need to double-ended wildcard in my example.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.