Home » SQL & PL/SQL » SQL & PL/SQL » REGEXP_REPLACE (Oracle 12c)
REGEXP_REPLACE [message #683146] Tue, 08 December 2020 10:29 Go to next message
sss111ind
Messages: 623
Registered: April 2012
Location: India
Senior Member

Hi All,

I have got some confusion over the result of the value please clarify it.



select 
regexp_replace('1000-','-','',1)c1,--the first "-" should be replaced hence expected 1000 (correct)
regexp_replace('1000-','-','',2)c2,--the second "-" should be replaced hence expected 1000- but result is 1000 (wrong)?
regexp_replace('-1000','-','',1)c3,--the first "-" should be replaced hence expected 1000 (correct)
regexp_replace('-1000','-','',2)c4--the second "-" should be replaced hence expected -1000  (correct)
from dual;

Thank you

[Updated on: Tue, 08 December 2020 10:30]

Report message to a moderator

Re: REGEXP_REPLACE [message #683148 is a reply to message #683146] Tue, 08 December 2020 10:52 Go to previous messageGo to next message
Michel Cadot
Messages: 67847
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

The fourth parameter of REGEXP_REPLACE is the position in the source string where Oracle should start to search for the pattern, the occurrence is the fifth parameter:
SQL> select
  2  regexp_replace('1000-','-','',1,1)c1,
  3  regexp_replace('1000-','-','',1,2)c2,
  4  regexp_replace('-1000','-','',1,1)c3,
  5  regexp_replace('-1000','-','',1,2)c4
  6  from dual;
C1   C2    C3   C4
---- ----- ---- -----
1000 1000- 1000 -1000

[Updated on: Tue, 08 December 2020 10:52]

Report message to a moderator

Re: REGEXP_REPLACE [message #683157 is a reply to message #683148] Tue, 08 December 2020 23:47 Go to previous message
sss111ind
Messages: 623
Registered: April 2012
Location: India
Senior Member

Thank you sir for clarifying this. I didn't referred the document properly.
Previous Topic: string_api package that... has no body?
Next Topic: ten-digit hexadecimal number
Goto Forum:
  


Current Time: Wed May 12 04:18:23 CDT 2021