replace inside a string

View All Scripts Login to Run Script

Script Name replace inside a string
Description demonstrates how to insert a string at a specific location of another string.
Area SQL General
Contributor NepoBot
Created Monday December 04, 2017

Statement 1

Create base table to hold test values

create table table1( 
    field1 varchar2(255), 
    field2 varchar2(255) 
)

Table created.

Statement 2

insert into table1(field1, field2) values('this is 1983', 'THE YEAR')

1 row(s) inserted.

Statement 3

insert into table1(field1, field2) values('this is 1983', null)

1 row(s) inserted.

Statement 4

select * from table1


FIELD1 FIELD2
this is 1983  - 
this is 1983 THE YEAR

2 rows selected.

Statement 5
^(.*) identifies any number of characters at the beginning of the string. (\d{4})$ identifies 4 numbers at the end of the string. The parenthesis around these tell the regex engine to remember these values. \1 = the first part of the string. \2 = the 4 digits at the end of the string. '\1 ' || field2 || '\2' will inject field 2 value in between the two captured values.
```
select regexp_replace(
         field1,                -- field to break apart
         '^(.*) (\d{4})$',      -- regular expression building groups
         '\1 '||field2|| ' \2') -- specify how the groups should join together
from table1
```
```
REGEXP_REPLACE(FIELD1,'^(.*)(\D{4})$','\1'||FIELD2||'\2')
this is  1983
this is THE YEAR 1983

2 rows selected.
```
Statement 6
if field2 is not null then perform the update otherwise return the value unchanged. I could have added this condition to the where clause but I wanted to demonstrate how that is not needed in case you wish to see all values.
```
select  
  case  
    when field2 is not null then regexp_replace(field1,'^(.*) (\d{4})$', '\1 '||field2|| ' \2') 
    else field1 
  end test_result 
from table1
```
```
TEST_RESULT
this is 1983
this is THE YEAR 1983

2 rows selected.
```

FIELD1	FIELD2
this is 1983	-
this is 1983	THE YEAR

REGEXP_REPLACE(FIELD1,'^(.*)(\D{4})$','\1'\|\|FIELD2\|\|'\2')
this is 1983
this is THE YEAR 1983

TEST_RESULT
this is 1983
this is THE YEAR 1983

Statement 7

Apply the above logic to the table permanently

update table1 
  set field1 = case  
    when field2 is not null then regexp_replace(field1,'^(.*) (\d{4})$', '\1 '||field2|| ' \2') 
    else field1 
  end

2 row(s) updated.

Statement 8

select * from table1


FIELD1 FIELD2
this is 1983  - 
this is THE YEAR 1983 THE YEAR

2 rows selected.

FIELD1	FIELD2
this is 1983	-
this is THE YEAR 1983	THE YEAR

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database