Skip to Main Content
  • Questions

  • Can I replace just the first occurrence of the string?

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Tony.

Asked: September 14, 2003 - 11:03 pm UTC

Last updated: April 09, 2012 - 3:35 pm UTC

Version: 8.1.6

Viewed 10K+ times! This question is

You Asked

Hi Tom,
I want to replace only the first occurrence of the string, and I used replace command as below:
select replace('EMPLOYER_TYPE,EMPLOYER_TYPE_DET','EMPLOYER_TYPE','-') from dual;
But it gave me the o/p:
REPLACE
-------
-,-_DET

I just want the o/p: -,EMPLOYER_TYPE_DET

Could you please show me how to do this?
Kind Regards,



and Tom said...

we can use case or decode to figure out if we need to replace something and then substr it in. like this:

ops$tkyte@ORA920> select x,
2 decode( nvl(instr(x,'EMPLOYER_TYPE'),0), 0, x,
3 substr(x,1,instr(x,'EMPLOYER_TYPE')-1) || '-'
4 || substr(x,instr(x,'EMPLOYER_TYPE')+length('EMPLOYER_TYPE')) ) r
5 from t;

X R
---------------------------------------- -----------------------------------
EMPLOYER_TYPE,EMPLOYER_TYPE_DET -,EMPLOYER_TYPE_DET
how now brow cow how now brow cow

xxxx EMPLOYER_TYPE,EMPLOYER_TYPE_DET xxxx -,EMPLOYER_TYPE_DET


Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks a lot!

A reader, September 15, 2003 - 5:34 pm UTC


replace last occurrence

Ved, June 15, 2011 - 4:04 am UTC

What if in the above question I just need to replace the last occurrence..

for eg.. say my string is

31-JAN-01-0.. my final string should be 31-JAN-01-

31-JAN-01-01-0... should be 31-JAN-01-01-

Thanks
Ved

Tom Kyte
June 17, 2011 - 12:10 pm UTC

do you want to just get rid of the last character?

what if your string was "31-jan-01-0hello world"

just the last character

Ved, June 20, 2011 - 10:33 am UTC

Sorry I was not clear..

create table test ( colA varchar2(20), colb number ) ;

insert into test values ('13-jun-01-01-1', 1 ) ;
insert into test values ('31-JUN-01-0', 0 ) ;
insert into test values ('31-JUN-01-001', 0 ) ;

The output should be
13-jun-01-01
31-JUN-01
31-JUN-01-001

Basically, whenever the last character along with the hyphen matches the value with the colB, I want to update it to a value minus the hyphen and colB.

One way I found out was

substr(cola, 1, (instr(cola, '-' || colb, -1,1) -1 ))

Is there a better way..

Tom Kyte
June 20, 2011 - 1:52 pm UTC

looks good to me.

Anything On Last Occurrance in a String

Jim Cox, April 09, 2012 - 12:57 pm UTC

Hi Tom

was wondering if you had a way to locate the last occurrence of a character starting at the end of the string and pulling out everything after that character

I am using 10.2.0.5
example of strings I am looking at are

my document.txt
my document version 1.0.txt
my_document 2.3.pdf

i need to find a way to start at the end of the string, searching for the period and extracting out the extension.

Any help would be appreciated

Jim
Tom Kyte
April 09, 2012 - 3:35 pm UTC 

ops$tkyte%ORA11GR2> select substr( txt, instr( txt, '.', -1 ) ) from (select 'my file.txt' txt from dual);

SUBS
----
.txt



read up on instr, it can search backwards, forwards and look for the Nth occurrence.

Anything On Last Occurrance in a String - Thanks

Jim Cox, April 20, 2012 - 2:38 pm UTC

Thanks Tom

Jim
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.