2

I have the below condition.

Monday 8:30 a.m. to 6:00 p.m. <.br> Tuesday Closed <.br> Wednesday 8:30 a.m. to 6:00 p.m.

Here Tuesday is Closed. So i need to exclude the below value from the string '<.br>Tuesday Closed' and generate as :

Monday 8:30 a.m. to 6:00 p.m. <.br> Wednesday 8:30 a.m. to 6:00 p.m.

Tried with REGEXP_REPLACE(OfficeHrs,'Closed',' ') which will only emit the Closed part, but not sure how to ignore the <.br> from the string

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
Tedgeor
  • 53
  • 1
  • 5

3 Answers3

1

You'd better using regexp_substr() instead of regexp_replace(), and instr(), regexp_count() as auxiliary functions, and listagg() to concatenate at the end :

with tab as
(
 select 'Monday 8:30 a.m. to 6:00 p.m.
 Tuesday Closed
 Wednesday 8:30 a.m. to 6:00 p.m' as str from dual 
), t1 as
(
select regexp_substr(str,'^.*$',1,level,'m') as str, level as lvl
  from tab
 connect by level <= regexp_count(str,chr(10)) + 1
)
select listagg(str,chr(10)) within group (order by lvl) as "Result String"
  from t1
 where instr(str,'Closed')=0;

Result String
---------------------------------
Monday 8:30 a.m. to 6:00 p.m.
 Wednesday 8:30 a.m. to 6:00 p.m

Demo

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
1

You can try /[^(<.br>)]*Closed\s?<\.br>/ to match and then replace it with '' Hope it helps

Tridibesh Nag
  • 52
  • 4
  • The question is tagged SQL and Oracle. What's with the forward slashes? That looks rather like `sed` or such, nothing like Oracle SQL. Besides, what do you think `[^(<.br>)]` means? What it does mean is "any single character other than an opening parenthesis, a less-than symbol, a period, the lower-case letter b, the lower-case letter r, a greater-than symbol, or a closing parenthesis". Is that what you thought it means? –  Jul 26 '19 at 04:51
1

you can try this one:

 with tab as(
  select 'Monday 8:30 a.m. to 6:00 p.m. <.br> Tuesday Closed <.br> Wednesday 8:30 a.m. to 6:00 p.m.' as str from dual union all
  select 'Monday 8:30 a.m. to 6:00 p.m. <.br> Tuesday Closed <.br> Wednesday 8:30 a.m. to 6:00 p.m. <.br> Thursday Closed <.br> Sunday 8:30 a.m. to 6:00 p.m.' as str from dual union all
  select 'Monday 8:30 a.m. to 6:00 p.m. <.br> Tuesday Closed <.br> Wednesday Closed <.br> Sunday 8:30 a.m. to 6:00 p.m.' as str from dual
)
select regexp_replace(str,'> [[:alpha:]]* Closed <.br')

from tab;

| REGEXP_REPLACE(STR,'>[[:ALPHA:]]*CLOSED<.BR')                                                            |
| :------------------------------------------------------------------------------------------------------- |
| Monday 8:30 a.m. to 6:00 p.m. <.br> Wednesday 8:30 a.m. to 6:00 p.m.                                     |
| Monday 8:30 a.m. to 6:00 p.m. <.br> Wednesday 8:30 a.m. to 6:00 p.m. <.br> Sunday 8:30 a.m. to 6:00 p.m. |
| Monday 8:30 a.m. to 6:00 p.m. <.br> Sunday 8:30 a.m. to 6:00 p.m.                                        |

db<>fiddle here

hotfix
  • 3,376
  • 20
  • 36
  • Thank you, this works as expected **regexp_replace(str,'<.br>[[:alpha:]]* Closed')** – Tedgeor Jul 25 '19 at 17:33
  • 1
    This will work, as long as <.br> are the only possible tags... and also only as long as the days "the business is Closed" are not either the first day or the last day in the list. –  Jul 26 '19 at 04:53
  • @mathguy that's true. since the pattern is always the same, it should not be a problem – hotfix Jul 26 '19 at 05:22