replace with question mark in the filename [message #656719] |
Sat, 15 October 2016 05:12 |
pointers
Messages: 451 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 |
|
Michel Cadot
Messages: 68665 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
|
|
|
|