CODEWITHSUNDEEP
sqloracle-database
arsha
arsha

Reputation: 67

regexp replace to replace the second special character from the last in a string

I have to replace last 2 special characters in my string .I tried the below options

It replaces the last special character from my string

select regexp_replace(trim('Clifton/RosalieBolner''49-S.R.'),'([^0-9A-Za-z]+)$','') from dual;

It replaces all the special characters in my string

select regexp_replace(trim('Clifton/RosalieBolner''49-S.R.'),'[^0-9A-Za-z]','') from dual;

I need a function that replaces only the last 2 special characters in the string. 

e.g 1 Clifton/RosalieBolner''49-S.R.      should be 
      Clifton/RosalieBolner''49-SR  

e.g 2 Hydroworx-Hydrotherapy-Hydrotr      should be 
      HydroworxHydrotherapyHydrotr   

e.g 3 Lenore&BillAlexander'35/Faulk       should be 
      Lenore&BillAlexander35Faulk

Upvotes: 1

Views: 440

Answers (4)

Andrei Odegov
Andrei Odegov

Reputation: 3429

Why not use backreferences? Further information can be found on the page for the REGEXP_REPLACE function.
The CROSS APPLY clause of the SELECT statement was used to move the calculation of the cleansed_s "variable" into the JOIN area and only specify it once.
The x in the match_parameter parameter of the REGEXP_REPLACE function allows you to ignore whitespace character in a pattern.
Whitespace characters were included in the pattern using the alternative quoting mechanism for string literals to improve a readability. But, given that Oracle supports some commonly used PERL regular expression operators, it's possible to write the call of the REGEXP_REPLACE function more briefly: regexp_replace(s, '(\W(\w*))?\W(\w*)$', '\2\3').

with
  t as(
    select
      'Clifton/RosalieBolner''49-S.R.' as s,
      'Clifton/RosalieBolner''49-SR' as expected_result
    from dual union all
    select
       'Hydroworx-Hydrotherapy-Hydrotr',
       'HydroworxHydrotherapyHydrotr'
    from dual union all
    select
      'Lenore&BillAlexander''35/Faulk',
      'Lenore&BillAlexander35Faulk'
    from dual union all
    select 'foo', 'foo' from dual union all
    select 'foo&bar', 'foobar' from dual union all
    select '..', '' from dual
)
select
  t.*,
  ca.*,
  case
    when t.expected_result is null or
         t.expected_result = ca.cleansed_s then 'passed'
    else 'not passed'
  end as test_result
from t
cross apply (
  select regexp_replace(s,
           q'% ([^[:alnum:]]([[:alnum:]]*))?
               [^[:alnum:]]([[:alnum:]]*)$ %',
           '\2\3', 1, 0, 'x') as cleansed_s from dual
) ca;

Result:

+--------------------------------+------------------------------+------------------------------+-------------+
|               S                |       EXPECTED_RESULT        |          CLEANSED_S          | TEST_RESULT |
+--------------------------------+------------------------------+------------------------------+-------------+
| Clifton/RosalieBolner'49-S.R.  | Clifton/RosalieBolner'49-SR  | Clifton/RosalieBolner'49-SR  | passed      |
| Hydroworx-Hydrotherapy-Hydrotr | HydroworxHydrotherapyHydrotr | HydroworxHydrotherapyHydrotr | passed      |
| Lenore&BillAlexander'35/Faulk  | Lenore&BillAlexander35Faulk  | Lenore&BillAlexander35Faulk  | passed      |
| foo                            | foo                          | foo                          | passed      |
| foo&bar                        | foobar                       | foobar                       | passed      |
| ..                             |                              |                              | passed      |
+--------------------------------+------------------------------+------------------------------+-------------+

Demo.

Upvotes: 1

Gary_W
Gary_W

Reputation: 10360

Here's a different way of looking at it. Use REGEXP_REPLACE() to group the different parts of the string, then only display the groups you want. Here the regex specifically groups the last 2 non-alphnumeric characters with optional strings of any characters before, after, or in between but when they occur at the end of the string. Replace all of that with groups 1, 3 and 5 which is all but the 2 non-alpha-numeric characters.

with test(id, col) as (
  select 1, 'Clifton/RosalieBolner''49-S.R.' from dual union all
  select 2, 'Hydroworx-Hydrotherapy-Hydrotr' from dual union all
  select 3, 'Lenore&BillAlexander''35/Faulk' from dual union all
  select 4, 'This-is#just)an_example'        from dual union all
  select 5, 'Test line 5 special *chars'     from dual union all
  select 6, 'TTest line 6^char'              from dual union all
  select 7, 'Thislineisoneword'              from dual union all
  select 8, '^^Thislineisonewordtoo'         from dual union all  
  select 9, NULL                             from dual
)
select id, col before,
  regexp_replace(col, '^(.*)?([^[:alnum:]])(.*)?([^[:alnum:]])(.*)?$', '\1\3\5') after
from test
order by id;


        ID BEFORE                         AFTER                         
---------- ------------------------------ ------------------------------
         1 Clifton/RosalieBolner'49-S.R.  Clifton/RosalieBolner'49-SR   
         2 Hydroworx-Hydrotherapy-Hydrotr HydroworxHydrotherapyHydrotr  
         3 Lenore&BillAlexander'35/Faulk  Lenore&BillAlexander35Faulk   
         4 This-is#just)an_example        This-is#justanexample         
         5 Test line 5 special *chars     Test line 5 specialchars      
         6 TTest line 6^char              TTest line6char               
         7 Thislineisoneword              Thislineisoneword             
         8 ^^Thislineisonewordtoo         Thislineisonewordtoo          
         9                                                              

9 rows selected.

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 142713

How about this? See comments within the code:

SQL> with test (col) as
  2    (select 'Clifton/RosalieBolner''49-S.R.' from dual union all
  3     select 'Hydroworx-Hydrotherapy-Hydrotr' from dual union all
  4     select 'Lenore&BillAlexander''35/Faulk' from dual union all
  5     select 'This-is#just)an_example'        from dual
  6    )
  7  select
  8    col,
  9    regexp_replace
 10      (col,
 11       '[^[:alnum:]]',                 -- replace non alphanumeric ...
 12       '',                             -- .. with an empty string
 13       -- starting at position calculated as position of ...
 14       regexp_instr(col,
 15                    '[^[:alnum:]]',    -- ... a non alphanumeric character
 16                    1,                 -- found from the beginning of the string
 17                    -- taking into account the last and previous to last occurence
 18                    -- calculated by regexp_count which finds number of non alphanumerics
 19                    greatest(regexp_count(col, '[^[:alnum:]]') - 1, 1)),
 20       0) result
 21  from test;

COL                            RESULT
------------------------------ ----------------------------------------
Clifton/RosalieBolner'49-S.R.  Clifton/RosalieBolner'49-SR
Hydroworx-Hydrotherapy-Hydrotr HydroworxHydrotherapyHydrotr
Lenore&BillAlexander'35/Faulk  Lenore&BillAlexander35Faulk
This-is#just)an_example        This-is#justanexample

SQL>

Upvotes: 2

hotfix
hotfix

Reputation: 3396

Maybe there is a better solution for the problem, but this one works

with str as(
select 'Clifton/RosalieBolner''49-S.R.' as txt from dual union all
select 'Hydroworx-Hydrotherapy-Hydrotr' as txt from dual union all
select 'Lenore&BillAlexander''35/Faulk' as txt from dual 
)
select REVERSE(regexp_replace(regexp_replace(REVERSE(t.txt),'[^A-Za-z0-9]','',1,1),'[^A-Za-z0-9]','',1,1)) as new_str
from str t;

first use a reverse funtion , then remove the first occurrence twice and then reverse again

TXT                            | REVERSE_TXT                    | NEW_STR                     
:----------------------------- | :----------------------------- | :---------------------------
Clifton/RosalieBolner'49-S.R.  | .R.S-94'renloBeilasoR/notfilC  | Clifton/RosalieBolner'49-SR 
Hydroworx-Hydrotherapy-Hydrotr | rtordyH-yparehtordyH-xrowordyH | HydroworxHydrotherapyHydrotr
Lenore&BillAlexander'35/Faulk  | kluaF/53'rednaxelAlliB&eroneL  | Lenore&BillAlexander35Faulk

db<>fiddle here

Upvotes: 1

Related Questions