| 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
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: 68641
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 |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
