CODEWITHSUNDEEP
regexoracle-database
Superdooperhero
Superdooperhero

Reputation: 8096

Oracle - Removing trailing characters from a string using a regular expression

I would like to remove trailing characters from a string using an Oracle regular expression.

My data looks like this:

TDC Supervisor #
TDC Supervisor ##
TDC Supervisor ###
Store Manager - 1
Store Manager - 2
Store Manager - 3
Stockroom Clerk Flexi 100 Hrs
Sales Consultant Flexi 120 Hrs
Service Desk Consultant (5 Day)
Secretary

I would like to get rid of the trailing # and - 1, but not the 120 Hrs and the 5 Day. Is there a way to do this using an Oracle regular expression. Not sure whether I should use REGEXP_SUBSTR or REGEXP_REPLACE.

So basically I would like to do a distinct that returns:

TDC Supervisor
Store Manager
Stockroom Clerk Flexi 100 Hrs
Sales Consultant Flexi 120 Hrs
Service Desk Consultant (5 Day)
Secretary

Upvotes: 0

Views: 1283

Answers (2)

wolφi
wolφi

Reputation: 8361

The hashes at the end are removed with #+$ (# for hash, + for one or more, $ for end of string)

The numbers are removed with - *\d+$ (- for minus, * for zero or more spaces, \d+ for one or more digit.

You can combine both with parens: (#+$)|(- *\d+$).

Zero or more spaces are removed with * (or \s* to catch other whitespace like tabs etc), so the final regular expression looks like \s*(#+$)|(-\s*\d+$).

CREATE TABLE mytable (mycolumn VARCHAR2(50));

INSERT INTO mytable VALUES ('TDC Supervisor #');
INSERT INTO mytable VALUES ('TDC Supervisor ##');
INSERT INTO mytable VALUES ('TDC Supervisor ###');
INSERT INTO mytable VALUES ('Store Manager - 1');
INSERT INTO mytable VALUES ('Store Manager - 2');
INSERT INTO mytable VALUES ('Store Manager - 3');
INSERT INTO mytable VALUES ('Stockroom Clerk Flexi 100 Hrs');
INSERT INTO mytable VALUES ('Sales Consultant Flexi 120 Hrs');
INSERT INTO mytable VALUES ('Service Desk Consultant (5 Day)');
INSERT INTO mytable VALUES ('Secretary');

select distinct regexp_replace(mycolumn,'\s*(#+$)|(-\s*\d+$)') 
  from mytable;

Service Desk Consultant (5 Day)
Store Manager
TDC Supervisor 
Secretary
Sales Consultant Flexi 120 Hrs
Stockroom Clerk Flexi 100 Hrs

Upvotes: 2

Egor Skriptunoff
Egor Skriptunoff

Reputation: 23737

regexp_replace(string, '\s*(-\s*\d+|#+)$')
fiddle

Upvotes: 1

Related Questions