How to apply regular expression on the below given string

Question

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 '*' based on a column value 'star_len' if the star_len = 2 the o/p = ''MCDONALD??_YYYYMMDD.TXT' if the star_len = 1 the o/p = ''MCDONALD?_YYYYMMDD.TXT'

Try again. Will your string ALWAYS include a period and an extension? And right before the period, the character "underscore" followed by eight digits? (I assume it's digits and not the letters Y, M and D, right?) Please don't play the "guess my requirement" game with us. — , Aug 01 '17 at 19:11
yes. In the real time data it will be like MCDONALD_20170501.TXT and if the star_len = 1 then the o/p should be = MCDONALD?_20170501.TXT — Rak kundra, Aug 01 '17 at 19:14
You changed from asterisk \*\* to question mark ?? - why? (Perhaps the Stack Overflow editor is giving you trouble... if you don't see the asterisk, escape it with a backslash, like so: \\*... or wrap everything within back-quotes to render as code snippet.) — , Aug 01 '17 at 19:17

score 1 · Answer 1 · answered Aug 01 '17 at 19:49

1

with
     inputs ( filename, position, symbol, len ) as ( 
       select 'MCDONALD_20170812.TXT', 9, '*', 2 from dual
     )
-- End of simulated inputs (for testing purposes only, not part of the solution).
-- SQL query begins BELOW THIS LINE.
select substr(filename, 1, position - 1) || rpad(symbol, len, symbol) 
                                         || substr(filename, position) as new_str
from   inputs
;

NEW_STR
-----------------------
MCDONALD**_20170812.TXT

answered Aug 01 '17 at 19:49

Bingo !! perfect its works fine now .I would he artfully thank you for your time :) – Rak kundra Aug 01 '17 at 19:52
@SanthoshPogaku - If the question is answered, you should choose a correct answer (and perhaps recognize other helpful answers by upvoting them too). – Aug 01 '17 at 20:45

Patrick H · Answer 2 · 2017-08-01T19:47:37.673

0

select regexp_replace('MCDONALD_YYYYMMDD.TXT','MCDONALD','MCDONALD' || 
          decode(star_len,1,'*',2,'**'))
from dual

This is how you could do it. I don't think you need it as a regular expression though if it is always going to be "MCDONALD".

EDIT: If you need to be providing the position in the string as well, I think a regular old substring should work.

select substr('MCDONALD_YYYYMMDD.TXT',1,position-1) || 
          decode(star_len,1,'*',2,'**') || substr('MCDONALD_YYYYMMDD.TXT',position) 
from dual

Where position and star_len are both columns in some table you provide(instead of dual).

EDIT2: Just to be more clear, here is another example using a with clause so that it runs without adding a table in.

    with testing as
(select 'MCDONALD_YYYYMMDD.TXT' filename,
        9 positionnum,
        2 star_len
from dual)

select substr(filename,1,positionnum-1) || 
            decode(star_len,1,'*',2,'**') || 
            substr(filename,positionnum)
from testing

edited Aug 01 '17 at 19:47

answered Aug 01 '17 at 19:15

Patrick H

653
6
14

This looks pretty impressive but what if i have a column 'position' = 9 and ' star_len ' = 1 then my o/p = 'MCDONALD?_123456.TXT' how to write based on this condition – Rak kundra Aug 01 '17 at 19:23
@SanthoshPogaku - what do you mean by " 'position' = 9? Are you appending at a fixed position, not right before the underscore and date? You should make that clearer in the original post. If that is indeed your requirement, then it is MUCH easier than any regular expression solutions, but make sure the original question is clear. – Aug 01 '17 at 19:27
i am sorry it is not a fixed position sir it need to be dependent on the position value column . how to handle that scenario ? – Rak kundra Aug 01 '17 at 19:34
Check my recent edit then. Does it HAVE to be a regular expression? We might need more information on the problem? – Patrick H Aug 01 '17 at 19:35
Nope we can use any function sir rather than regular expression – Rak kundra Aug 01 '17 at 19:39
Does that substring version in my EDIT section work for you then? – Patrick H Aug 01 '17 at 19:41
@SanthoshPogaku - OK, so the position to insert is given. Is it guaranteed that the input filename is at least that long? So let's say the position is given as 9, is it then guaranteed that the input has at least 9 characters? If not, what is the desired result? – Aug 01 '17 at 19:45
yes sir the input will be always be more thatn 9 characters its defined like that – Rak kundra Aug 01 '17 at 19:47
Thank you sir i got the solution for the question :) – Rak kundra Aug 01 '17 at 19:52

score 0 · Answer 3 · answered Aug 01 '17 at 20:56

For the fun of it, here's a regex_replace solution. I went with a star since that what your variable was called even though your example used a question mark. The regex captures the filename string in 2 parts, the first being from the start up to 1 character before the position value, the second the rest of the string. The replace puts the captured parts back together with the stars in between.

with tbl(filename, position, star_len ) as ( 
       select 'MCDONALD_20170812.TXT', 9, 2 from dual
)
select regexp_replace(filename, 
       '^(.{'||(position-1)||'})(.*)$', '\1'||rpad('*', star_len, '*')||'\2') as fixed
from tbl;

If you are using `REGEXP_REPLACE()`, you don't need the `(.*)$` bit in the match pattern - just remove it and also `||'\2'` from the replacement string. Like so: `select regexp_replace(filename, '^(.{'||(position-1)||'})', '\1'||rpad('*', star_len, '*')) as fixed` — , Aug 02 '17 at 01:13

How to apply regular expression on the below given string

3 Answers3