Re: replace substring

From: TurkBear <johng_at_mm.com>
Date: 2000/05/23
Message-ID: <392af012.12017770@news.news-ituk.to>#1/1

Use the function in an Update statement:

Look :

---

SQL> create table test_mytext(
  2 mytext varchar2(45));

Table created.

SQL> insert into test_mytext values ('Change');

1 row created.
  1* insert into test_mytext values ('replace') 1 row created.

SQL> update test_mytext set mytext = replace(mytext,'Change','NewChange') ;

2 rows updated.

SQL> select * from test_mytext;

MYTEXT

---

NewChange
replace

---

Hope it helps...

GHouck <hksys_at_teleport.com> wrote:

>Svein Fossdal wrote:
>>
>> Thanx for your answer - this seems to be the function I was looking for.
>>
>> But how do you use it on columns in a table?
>> For example, if we have the table TEST with a column TEXT and
>> we want to replace all occurences of a substring of TEXT in all rows.
>> I tried
>> select replace(select tekst from test,'e','E') from TEST;
>>
>> but it seems to want a pure text string as an input.
>>
>> How do I use this function to change every occurence of a
>> specific substring to a new one in a column of a table, for
>> all rows?
>>
>> Thanx in advance.
>>
>> Regards,
>>
>> Svein Fossdal
>>
>> Michel Cadot <micadot_at_netcourrier.com> wrote in message
>> news:8ge4hu$40p$1_at_news2.isdnet.net...
>> > What about the replace function of SQL?
>> >
>> > REPLACE
>> > Syntax REPLACE(char, search_string[,replacement_string])
>> >
>> > Purpose
>> >
>> > Returns char with every occurrence of search_string replaced
>> > with replacement_string. If replacement_string is omitted or
>> > null, all occurrences of search_string are removed. If
>> > search_string is null, char is returned. This function provides
>> > a superset of the functionality provided by the TRANSLATE
>> > function. TRANSLATE provides single character, one to one,
>> > substitution. REPLACE allows you to substitute one string
>> > for another as well as to remove character strings.
>> >
>> > Example
>> >
>> > SELECT REPLACE('JACK and JUE','J','BL') "Changes"
>> > FROM DUAL
>> >
>> > Changes
>> > --------------
>> > BLACK and BLUE
>> >
>> > --
>> > Have a nice day
>> > Michel
>> >
>> >
>> > Svein Fossdal <sveifo_at_frisurf.no> a écrit dans le message :
 mfwW4.10645$C9.190172_at_news1.online.no...
>> > > Hello
>> > >
>> > > Does anybody have a good function or procedure that
>> > > finds every occurence of a given substring and changes
>> > > this to a new one?
>> > > A typical function call would be something like
>> > > search_and_replace('old_substring', 'new_substring', column, table)
>> > >
>> > > I am very greatful for any help.
>> > >
>> > > Regards
>> > > Svein Fossdal
>> > >
>> > >
>> >
>> >
>
>Try:
>
> update mytable set myCol = replace(myCol,'oldSubStr','newSubStr');
>
>Yours,
>
>Geoff Houck
>systems hk
>hksys_at_teleport.com
>http://www.teleport.com/~hksys

-----= Posted via Newsfeeds.Com, Uncensored Usenet News =----- http://www.newsfeeds.com - The #1 Newsgroup Service in the World! -----== Over 80,000 Newsgroups - 16 Different Servers! =----- Received on Tue May 23 2000 - 00:00:00 CDT