如何在 Oracle 中查找和替换文本?
问题陈述:您想在 Oracle 中查找和替换字符串。
解决方案:
- 函数:TRANSLATE
语法:TRANSLATE(expr, from_string, to_string)
Oracle 中的 TRANSLATE 函数允许您在单个操作中进行多个单个字符的一对一替换。
但是，to_string 和 from_string 值不能为空，如果将空字符串传递给 TRANSLATE 函数，Oracle 数据库会将空字符串解释为空并返回空值。
/* 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 函数可用于将一个字符串替换为另一个字符串。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;
输出
back 和 bill jack 和 jill
示例
/* 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(source_text, pattern, replacement_string, position, indication, options)
REGEXP_REPLACE 函数是查找和替换文本主体中的字符串的绝佳方法。该函数可用于任何 Oracle SQL 语句或 PL/SQL 代码中
pattern:用于匹配的正则表达式或文本字符串。
替换字符串:将替换所标识字符串的每个出现的字符串。
position:可选，如果指定，Oracle 将从此位置在源文本中的指定位置进行搜索。默认情况下，位置为 1。
occurrence:可选的发生参数是一个非负整数，表示替换操作的发生。
如果指定 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;
