Home » SQL & PL/SQL » SQL & PL/SQL » replace with question mark in the filename (oracle 11.2.0.3)
replace with question mark in the filename [message #656719] Sat, 15 October 2016 05:12 Go to next message
pointers
Messages: 439
Registered: May 2008
Senior Member
Hi,

I have a table which have two columns, one column contains file names and second column contains date formats in the file name.

Now i would like to replace the date format in the filename with question mark(?)
Number of question marks should be equal to the length of the date format. How to achieve this with regular expressions.

for e.g.
WITH t AS
(SELECT 'ABCD_ACCRE_ACC_YYYYMMDD.txt' str, 'YYYYMMDD' dt_format
   FROM DUAL
UNION ALL
SELECT 'ABCD_USA_ACC_YYYYMMDDHHMISS.txt', 'YYYYMMDDHHMISS'
  FROM DUAL)
SELECT str, dt_format, REGEXP_REPLACE (str, dt_format, '?') new_str
FROM t;

Expected Output:

STR                                DT_FORMAT         NEW_STR
ABCD_ACCRE_ACC_YYYYMMDD.txt        YYYYMMDD          ABCD_ACCRE_ACC_????????.txt
ABCD_USA_ACC_YYYYMMDDHHMISS.txt    YYYYMMDDHHMISS    ABCD_USA_ACC_??????????????.txt

Thank you in advance.

Regards,
Pointers
Re: replace with question mark in the filename [message #656721 is a reply to message #656719] Sat, 15 October 2016 05:19 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

A simple REPLACE works:
SQL> col new_str format a31
SQL> WITH t AS
  2  (SELECT 'ABCD_ACCRE_ACC_YYYYMMDD.txt' str, 'YYYYMMDD' dt_format
  3     FROM DUAL
  4  UNION ALL
  5  SELECT 'ABCD_USA_ACC_YYYYMMDDHHMISS.txt', 'YYYYMMDDHHMISS'
  6    FROM DUAL)
  7  select str, dt_format, replace(str,dt_format,rpad('?',length(dt_format),'?')) new_str
  8  from t
  9  /
STR                             DT_FORMAT      NEW_STR
------------------------------- -------------- -------------------------------
ABCD_ACCRE_ACC_YYYYMMDD.txt     YYYYMMDD       ABCD_ACCRE_ACC_????????.txt
ABCD_USA_ACC_YYYYMMDDHHMISS.txt YYYYMMDDHHMISS ABCD_USA_ACC_??????????????.txt
Re: replace with question mark in the filename [message #656723 is a reply to message #656721] Sat, 15 October 2016 06:12 Go to previous message
pointers
Messages: 439
Registered: May 2008
Senior Member
This helps me. Thank you Micheal.

Regards,
Pointers
Previous Topic: Confirming Custom Raised Error Exceptions
Next Topic: star keyword oracle
Goto Forum:
  


Current Time: Tue Jan 16 16:17:06 CST 2018

Total time taken to generate the page: 0.07797 seconds