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

Question

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

Removing the trailing characters is done in order to get less distinct results yes. — Superdooperhero
– Superdooperhero, Commented Jul 23, 2014 at 11:21

wolφi · Accepted Answer · 2014-07-23 11:53:52Z

2

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

edited Jul 23, 2014 at 11:53

answered Jul 23, 2014 at 11:38

wolφi

8,3914 gold badges42 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

wolφi Over a year ago

@Egor Skriptunoff's \s are safer than my ` `, they catch tabs and other whitespace. And I forgot the space before the hashes. +1 to Egor.

Egor Skriptunoff · Accepted Answer · 2014-07-23 11:34:11Z

1

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

answered Jul 23, 2014 at 11:34

Egor Skriptunoff

23.8k2 gold badges38 silver badges68 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related