How to replace part of character string

[moepower]

Does Oracle have a command to replace part of a character string in a field? ie. If I have a field called LastName which has a value of "Smithy", if I want to change the value to "Smacky" is it possible to search for "ith" and replace it with "ack"? Thanks,

 

[karluk]

Please read the documentation on the REPLACE function. It does this type of substitution.

 

[UDIT]

HI THERE ,

(take care about the CASE ie upper caes or lower case letters)

FOR GENERALIZED SEARCH AND THEN REPLACING FOR DISPLAY PURPOSE:
select replace(lastname,'IT','AC') from TABLENAME where lastname LIKE '%IT%';

for permanent replacement:
alter table TABLENAME set lastname='SMACKY' WHERE lastname='SMITHY';

I HOPE THAT HELPS.
UDIT

 

[moepower]

Thanks guys. It works like a charm.

 

[UDIT]

oops sorry i made a mistake in that PERMANENT query solution above , instead of ALTER TABLE , it should be

Update Table_name set lastname='SMACKY' WHERE lastname='SMITHY';

udit.

 

[MikeLacey]

Udit -- if you Red Flag your own post above, where you said alter table by mistake, and explain what went wrong tek-tips will change it for you. Mike
michael.j.lacey@ntlworld.com
Email welcome if you're in a hurry or something -- but post in tek-tips as well please, and I will post my reply here as well.