Home » SQL & PL/SQL » SQL & PL/SQL » regexp_replace (Oracle 12c)
regexp_replace [message #677082] Fri, 16 August 2019 08:57 Go to next message
sss111ind
Messages: 608
Registered: April 2012
Location: India
Senior Member

Hi All,

I want to use only single function for below requirement,is it possible ?




--INPUT 
--777XX REQ MSD 20022019 3

--OUTPUT
--777XX RES MSD 16082019 1

SELECT
    replace(regexp_substr('777XXREQMSD200220193', '^\d{3}[A-Z]+'), 'REQ', 'RES')
    || TO_CHAR(SYSDATE, 'DDMMYYYY')||'1'
FROM
    dual;


Thanks

Re: regexp_replace [message #677085 is a reply to message #677082] Fri, 16 August 2019 10:04 Go to previous messageGo to next message
Michel Cadot
Messages: 66554
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

The rule is?

Re: regexp_replace [message #677102 is a reply to message #677085] Mon, 19 August 2019 02:36 Go to previous messageGo to next message
sss111ind
Messages: 608
Registered: April 2012
Location: India
Senior Member

Thank you for reply.

The rule is

1.REQ should be replaced to RES.
2.Date part should be appended in the last part following 1.

I have given example above with input and output.
Re: regexp_replace [message #677103 is a reply to message #677102] Mon, 19 August 2019 02:50 Go to previous messageGo to next message
Michel Cadot
Messages: 66554
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

What is the problem with your query?
What is the general format of your input? A single example does not allow to give an appropriate query.

[Updated on: Tue, 20 August 2019 12:19]

Report message to a moderator

Re: regexp_replace [message #677105 is a reply to message #677103] Mon, 19 August 2019 03:55 Go to previous messageGo to next message
sss111ind
Messages: 608
Registered: April 2012
Location: India
Senior Member

Sorry for confusion , please find the explanation.

input- 777XX REQ MSD 20022019
output-777XX RES MSD 16082019 (changes done RES and date only rest everything same)

FIRST- 3 digit 777 (OUTPUT-same as input)
NEXT- any number of only characters(alpha) in that REQ must be there(OUPUT- RES should come inplace of REQ)
LAST- part DATE (OUTPUT-TODAYS date should come ignoring above date)

Can it be done using a single function as I have done using 2 functions.

Thanks.

[Updated on: Mon, 19 August 2019 03:57]

Report message to a moderator

Re: regexp_replace [message #677107 is a reply to message #677105] Mon, 19 August 2019 10:25 Go to previous messageGo to next message
Michel Cadot
Messages: 66554
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select regexp_replace(
  2           '777XXREQMSD200220193',
  3           '^(\d{3}[A-Z]*)REQ([A-Z]*)\d+$',
  4           '\1RES\2'||TO_CHAR(SYSDATE, 'DDMMYYYY')||'1') res
  5  from dual
  6  /
RES
---------------------------------------------------------------------
777XXRESMSD190820191

1 row selected.
icon14.gif  Re: regexp_replace [message #677114 is a reply to message #677107] Tue, 20 August 2019 01:50 Go to previous message
sss111ind
Messages: 608
Registered: April 2012
Location: India
Senior Member

Thank you Michel Sir for a awesome answer.

[Updated on: Tue, 20 August 2019 01:51]

Report message to a moderator

Previous Topic: xml generation from db
Next Topic: Bursting Program is not being Submitted
Goto Forum:
  


Current Time: Thu Sep 19 01:15:38 CDT 2019