如何在Oracle中查找和替换文本？
问题陈述：您想在Oracle中查找并替换一个字符串。
解：
函数：TRANSLATE
语法：TRANSLATE(expr,from_string,to_string)
oracle中的TRANSLATE函数允许您在单个操作中制作多个单个字符，一对一替换。
但是，to_string和from_string值不能为空，如果将空字符串传递给TRANSLATEFunction，Oracle数据库会将空字符串解释为null并返回null。
/* replace all occurances of b with j in below string */
SELECT 'back and bill ' AS original_string
,TRANSLATE('back and bill','b','j') AS replaced_string
FROM dual;输出结果
back and bill jack and jill
示例
/* to_string with empty value return null */
SELECT 'back and bill ' AS original_string
,TRANSLATE('back and bill','b','') AS replaced_string
FROM dual;输出结果
back and bill (null)
示例
/* from_string with empty value return null */
SELECT 'back and bill ' AS original_string
,TRANSLATE('back and bill','','j') AS replaced_string
FROM dual;输出结果
back and bill (null)
函数：REPLACE
语法：REPLACE(expr,search_string,replacement_string)
在oracle中替换功能，您可以将一个字符串替换为另一个字符串。REPLACE还允许您删除表达式中所有不需要的字符串。
如果search_string为空，则Oracle返回原始表达式，不对其进行任何更改。
示例
/* replace all occurances of b with j in below string */
SELECT 'back and bill ' AS original_string
,REPLACE('back and bill','b','j') AS replaced_string
FROM dual;输出
后背和票据千斤顶和吉尔
示例
/* to_string with empty will remove the search_string from the expression */
SELECT 'back and bill ' AS original_string
,REPLACE('back and bill','b','') AS replaced_string
FROM dual;输出结果
back and bill ack and ill
示例
/* search_string with empty value returns original expression */
SELECT 'back and bill ' AS original_string
,REPLACE('back and bill','','j') AS replaced_string
FROM dual;输出结果
back and bill back and bill
函数：REGEXP_REPLACE
语法：REGEXP_REPLACE（源文本，模式，replacement_string，位置，出现次数，选项）
REGEXP_REPLACE函数是在文本正文中查找和替换字符串的好方法。该函数可以在任何OracleSQL语句或PL/SQL代码中使用
pattern：用于匹配的正则表达式或文本字符串。
替换字符串：将替换每次出现的已标识字符串的字符串。
position：可选（如果已指定），Oracle将从指定的位置开始搜索，并在该位置的源文本中进行搜索。默认情况下，位置为1。
出现：可选的出现参数是一个非负整数，指示替换操作的出现。
如果指定为0，则所有匹配项将被替换。
如果指定了一个非0的正整数，则Oracle将用替换字符串替换该匹配项。
示例
SELECT REGEXP_REPLACE('aaabbbcccdddeeefff','[abcdef]','1') AS replaced_value FROM dual;
SELECT REGEXP_REPLACE('abc','[b]','01') AS replaced_value FROM dual;
SELECT REGEXP_REPLACE('123-45-6789','[[:digit:]]{3}-[[:digit:]]{2}-[[:digit:]]{4}','xxx-xxx-xxxx') AS replaced_value FROM dual;
-- SQL to identify name ROGER and replace it with Rafael.
SELECT full_name AS original_name,
REGEXP_REPLACE(full_name, 'Rogers','Rafael') AS replaced_name
FROM customers
WHERE REGEXP_INSTR(UPPER(full_name),'ROGER') > 0 ;输出结果
Walter Rogers Walter Rafael Kathryn Rogers Kathryn Rafael
示例
-- PL/SQL program to find and replace a string
-- Declare cursor to to extract rows
DECLARE
CURSOR cur IS
SELECT full_name
FROM customers;
rec cur%ROWTYPE;
new_name customers.full_name%TYPE;
BEGIN
FOR rec IN cur
LOOP
IF REGEXP_INSTR(UPPER(rec.full_name),'ROGER') > 0
THEN
new_name := REGEXP_REPLACE(rec.full_name, 'Rogers', 'Rafael');
DBMS_OUTPUT.PUT_LINE(' Original name ' || rec.full_name || ' is replaced with ' || new_name);
END IF;
END LOOP;
END;输出结果
Original name Walter Rogers is replaced with Walter Rafael Original name Kathryn Rogers is replaced with Kathryn Rafael
数据准备：用于该问题的数据如下所示。
示例
create table customers ( customer_id integer generated by default on null as identity, email_address varchar2(255 char) not null, full_name varchar2(255 char) not null) ; insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (1,'tammy.bryant@internalmail','Tammy Bryant'); insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (2,'roy.white@internalmail','Roy White'); insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (3,'gary.jenkins@internalmail','Gary Jenkins'); insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (4,'victor.morris@internalmail','Victor Morris'); insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (5,'beverly.hughes@internalmail','Beverly Hughes'); insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (6,'evelyn.torres@internalmail','Evelyn Torres'); insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (7,'carl.lee@internalmail','Carl Lee'); insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (8,'douglas.flores@internalmail','Douglas Flores'); insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (9,'norma.robinson@internalmail','Norma Robinson'); insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (10,'gregory.sanchez@internalmail','Gregory Sanchez'); insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (11,'judy.evans@internalmail','Judy Evans'); insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (12,'jean.patterson@internalmail','Jean Patterson'); insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (13,'michelle.ramirez@internalmail','Michelle Ramirez'); insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (14,'elizabeth.martinez@internalmail','Elizabeth Martinez'); insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (15,'walter.rogers@internalmail','Walter Rogers'); insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (16,'ralph.foster@internalmail','Ralph Foster'); insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (17,'tina.simmons@internalmail','Tina Simmons'); insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (18,'peter.jones@internalmail','Peter Jones'); insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (19,'kathryn.rogers@internalmail','Kathryn Rogers'); insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (20,'dennis.lopez@internalmail','Dennis Lopez'); insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (21,'martha.baker@internalmail','Martha Baker'); insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (22,'raymond.bailey@internalmail','Raymond Bailey'); insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (23,'christopher.allen@internalmail','Christopher Allen'); insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (24,'jonathan.coleman@internalmail','Jonathan Coleman'); insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (25,'walter.turner@internalmail','Walter Turner'); insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (26,'anna.murphy@internalmail','Anna Murphy'); insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (27,'carol.alexander@internalmail','Carol Alexander'); insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (28,'teresa.brown@internalmail','Teresa Brown'); insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (29,'beverly.rivera@internalmail','Beverly Rivera'); insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (30,'lisa.hughes@internalmail','Lisa Hughes'); insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (31,'deborah.taylor@internalmail','Deborah Taylor'); insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (32,'cheryl.young@internalmail','Cheryl Young'); COMMIT;