oracle中 怎么替换,如何在Oracle中查找和替换文本？

问题陈述：您想在Oracle中查找并替换一个字符串。

解：函数：TRANSLATE

语法：TRANSLATE(expr,from_string,to_string)

oracle中的TRANSLATE函数允许您在单个操作中制作多个单个字符，一对一替换。

但是，to_string和from_string值不能为空，如果将空字符串传递给TRANSLATE Function，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函数是在文本正文中查找和替换字符串的好方法。该函数可以在任何Oracle SQL语句或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;