Home » SQL & PL/SQL » SQL & PL/SQL » string operations (oracle 11g)
string operations [message #664746] Tue, 01 August 2017 12:58 Go to next message
POGAKU_SANTHOSH
Messages: 18
Registered: April 2017
Junior Member
hi team,

i have a string 'MCDONALD_YYYYMMDD.TXT' i need to use regular expressions and append the '**' after the letter 'D' in the string given . (i.e In the string at postion 9 i need to append two '**' to the string .How to do that ?

Thank you .
Re: string operations [message #664747 is a reply to message #664746] Tue, 01 August 2017 13:34 Go to previous messageGo to next message
BlackSwan
Messages: 25745
Registered: January 2009
Location: SoCal
Senior Member
use INSTR to locate "_"
use SUBSTR to extract characters to left of "_" (LEFT)
use SUBSTR to extract characters to right of "_" (RIGHT)

NEW_STRING := LEFT || '**' || RIGHT;
Re: string operations [message #664749 is a reply to message #664747] Tue, 01 August 2017 13:45 Go to previous messageGo to next message
POGAKU_SANTHOSH
Messages: 18
Registered: April 2017
Junior Member
can i get a practical example on that ?
Re: string operations [message #664750 is a reply to message #664746] Tue, 01 August 2017 13:56 Go to previous messageGo to next message
Michel Cadot
Messages: 65252
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

MCDONALD_YYYYMMDD.TXT'

Which D?

Re: string operations [message #664751 is a reply to message #664750] Tue, 01 August 2017 13:58 Go to previous messageGo to next message
POGAKU_SANTHOSH
Messages: 18
Registered: April 2017
Junior Member
second 'D' FROM STARTING .
Re: string operations [message #664752 is a reply to message #664749] Tue, 01 August 2017 13:58 Go to previous messageGo to next message
BlackSwan
Messages: 25745
Registered: January 2009
Location: SoCal
Senior Member
POGAKU_SANTHOSH wrote on Tue, 01 August 2017 11:45
can i get a practical example on that ?
You need to learn to Read The Fine Manual

http://docs.oracle.com/database/122/SQLRF/SUBSTR.htm#SQLRF06114

http://docs.oracle.com/database/122/SQLRF/INSTR.htm#SQLRF00651
Re: string operations [message #664754 is a reply to message #664751] Tue, 01 August 2017 14:03 Go to previous messageGo to next message
Michel Cadot
Messages: 65252
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
POGAKU_SANTHOSH wrote on Tue, 01 August 2017 20:58
second 'D' FROM STARTING .
SQL> select regexp_replace('MCDONALD_YYYYMMDD.TXT'...) from dual;
REGEXP_REPLACE('MCDONAL
-----------------------
MCDONALD**_YYYYMMDD.TXT

1 row selected.

Complete solution when you'll feedback and thank people in your previous topics (and feedbakc does not mean just thanks but answer the questions, explain the problem, the solution AND thank).

[Updated on: Tue, 01 August 2017 14:06]

Report message to a moderator

Re: string operations [message #664755 is a reply to message #664752] Tue, 01 August 2017 14:09 Go to previous messageGo to next message
POGAKU_SANTHOSH
Messages: 18
Registered: April 2017
Junior Member
Thank you swan will verify and solve it . Smile
Re: string operations [message #664756 is a reply to message #664754] Tue, 01 August 2017 14:09 Go to previous messageGo to next message
POGAKU_SANTHOSH
Messages: 18
Registered: April 2017
Junior Member
Yeah i will try to solve it once again by putting some additional stuff in the expression Smile Thank you
Re: string operations [message #664768 is a reply to message #664756] Wed, 02 August 2017 06:36 Go to previous message
Bill B
Messages: 1701
Registered: December 2004
Senior Member
The easiest way would be

select replace('MCDONALD_YYYYMMDD.TXT','D_','D**_') from dual;

by the way a regular replace will be much faster then a regexp_replace
Previous Topic: Regular Expression : Find and Replace Specific String
Next Topic: ORA-29279: SMTP permanent error: Unable to relay
Goto Forum:
  


Current Time: Wed Dec 13 11:51:36 CST 2017

Total time taken to generate the page: 0.06400 seconds