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: 39
Registered: April 2017
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: 26766
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: 39
Registered: April 2017
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: 68625
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: 39
Registered: April 2017
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: 26766
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: 68625
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: 39
Registered: April 2017
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: 39
Registered: April 2017
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: 1971
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: Fri Mar 29 05:45:16 CDT 2024