We help IT Professionals succeed at work.

substring update

ank5
ank5 asked
on
I have a table on Oracle which has one column say, email_address. All the addresses have _del in the end.
Can I write a SQL query to get rid of _del in the end. something like

abc@cf.com_del
xyz@cf.com_del
Comment
Watch Question

Production Manager / Application Support Manager
BRONZE EXPERT
Commented:
try :

select substr(col1,1,instr(col1,'_del')-1) from dual;

select substr('abc@cf.com_del',1,instr('abc@cf.com_del','_del')-1) from dual
Naveen KumarProduction Manager / Application Support Manager
BRONZE EXPERT

Commented:
if you want to update then :

update table1
set col1 = substr(col1,1,instr(col1,'_del')-1)
where .....
Naveen KumarProduction Manager / Application Support Manager
BRONZE EXPERT

Commented:
so if you column name is email_address then :

update table1
set email_address = substr(email_address,1,instr(email_address,'_del')-1)
where << .... >> ;

Just ensure that you have a where clause accordingly otherwise all records in the table will get updated accordingly
Naveen KumarProduction Manager / Application Support Manager
BRONZE EXPERT

Commented:
if you want to update only those records which have this _del at the end to get rid of the _del, then try the below with the where clause accordingly :

update table1
set email_address = substr(email_address,1,instr(email_address,'_del')-1)
where substr(email_address,-4) = '_del';
SILVER EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
A regular expression version:
select regexp_replace('abc@cf.com_del','(.*)(_del)','\1') from dual
SILVER EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
set email_address = regexp_replace(email_address,'_del$')

or to test it, just select the regexp


select regexp_replace(email_address,'_del$') from yourtable
johnsoneSenior Oracle DBA
BRONZE EXPERT

Commented:
REGEXP_REPLACE will probably be best, especially if you want to ensure that it is at the end of the field.

Instead of using SUBSTR in the original posting, I would suggest REPLACE:

update tbl
set col=replace(col, '_del')
where instr(col, '_del') > 0;
SILVER EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
>>set col=replace(col, '_del')

The original substr and this one can have nasty side effects.  

Consider:  abc_del@cf_del.com
awking00Information Technology Specialist
BRONZE EXPERT

Commented:
Or even the case of abc_del@cf_del.com_del or univ_of_del@univ_of_del.edu_del, both of which are accommodated by sdstuber's solution.
johnsoneSenior Oracle DBA
BRONZE EXPERT

Commented:
Agreed, that is why I said that REGEXP_REPLACE is probably the best.  Just wanted to put the fact that REPLACE is better than the SUBSTR and INSTR approach.
SILVER EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
SUBSTR/INSTR can still work,  you just need to make sure you search for _del backwards so you only pick up the end
and you need to check if the _del string exists and exists exactly a the end

something like this...


 CASE
           WHEN INSTR(email_address, '_del', -1) = LENGTH(email_address) - 3
           THEN
               SUBSTR(email_address, 1, INSTR(email_address, '_del', -1) - 1)
           ELSE
               email_address
       END

so yes,  I like regexp_replace better  too  :)
SILVER EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
ank5,

please explain why the 2 accepted solutions were chosen.


The first one isn't reliable, but a fixed version was posted.

The regexp will work, but a simpler and more efficient version was posted.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.