Home » SQL & PL/SQL » SQL & PL/SQL » REPLACING only on specified locations
REPLACING only on specified locations [message #279974] Mon, 12 November 2007 02:52 Go to next message
swapnajojo
Messages: 40
Registered: June 2007
Location: India
Member

Hi Team

How wil u replace only the 15th and 16th position with ''00' in '123456789123456789'
using the Replcae function .Is there any otherway for the same

Thanks In Advance
Binu
Re: REPLACING only on specified locations [message #279977 is a reply to message #279974] Mon, 12 November 2007 03:12 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
One way might be concatenating two substrings:
SELECT SUBSTR(your_string, 1, 14)
   || '00'
   ||  SUBSTR(your_string, 17, LENGTH(your_string)
FROM ...
Re: REPLACING only on specified locations [message #279978 is a reply to message #279974] Mon, 12 November 2007 03:27 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
I'd use substr but I was thinking that regular expressions must offer a way too. Any takers?

MHE

[Updated on: Mon, 12 November 2007 03:28]

Report message to a moderator

Re: REPLACING only on specified locations [message #279983 is a reply to message #279978] Mon, 12 November 2007 03:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select regexp_replace('123456789123456789','(.{14})..(.*$)','\100\2') from dual;
REGEXP_REPLACE('12
------------------
123456789123450089

1 row selected.

Regards
Michel
Re: REPLACING only on specified locations [message #279984 is a reply to message #279983] Mon, 12 November 2007 03:57 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Good one, Michel! My variant (work in progress) looked like this:
WITH yourtable AS
( SELECT '123456789123456789'  thetext FROM dual )
SELECT thetext
     , SUBSTR(thetext,1,14)||'00'||SUBSTR(thetext,17) newtext
     , REGEXP_REPLACE(thetext, '(..............)(..)(.*)','\100\3') regtext
FROM yourtable
/
I like your approach.

MHE
Re: REPLACING only on specified locations [message #279992 is a reply to message #279984] Mon, 12 November 2007 04:18 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Unfortunately, I can not use
Quote:

regexp_replace('123456789123456789','(.{14})..(.*$)','\100\2')
as my database doesn't speak French.
Re: REPLACING only on specified locations [message #279994 is a reply to message #279992] Mon, 12 November 2007 04:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Laughing

Re: REPLACING only on specified locations [message #279995 is a reply to message #279984] Mon, 12 November 2007 04:22 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Missed a ^ to start the search from first character:
SQL> select regexp_replace('123456789123456789','(^.{14})..(.*$)','\100\2') from dual;
REGEXP_REPLACE('12
------------------
123456789123450089

1 row selected.

Regards
Michel
Previous Topic: How to Bulk load an XML file content into the DB
Next Topic: how to find out name of table if we dont know it
Goto Forum:
  


Current Time: Sat Dec 03 03:41:13 CST 2016

Total time taken to generate the page: 0.13061 seconds