Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Extract String

slider rulesNov 22 2021 — edited Nov 22 2021

Hi,
I am using Oracle 12.1.2. I want to extract the value from the strings below that are before the numbers and hyphen
For example below are strings and required output
ABC-123-561944625-GGG_3
required output - ABC
yyyy-land-tm-942473-dl_hhh_5
required output - yyyy-land-tm
hjdhgjfhgj-130909-wtygj_jhgkj_2
required output - hjdhgjfhgj
mmmm-hytc-tm-yy-942473-dl_hhh_5
required output - mmmm-hytc-tm-yy
sjg-099-wtygj_jhgkj_111
required output - sjg
create table t1 (tst VARCHAR2(80));
insert into t1 values('mmmm-hytc-tm-yy-942473-dl_hhh_5');
insert into t1 values('hjdhgjfhgj-130909-wtygj_jhgkj_2');
insert into t1 values('yyyy-land-tm-942473-dl_hhh_5');
insert into t1 values('ABC-123-561944625-GGG_3');
insert into t1 values('sjg-099-wtygj_jhgkj_111');

Results:
mmmm-hytc-tm-yy
hjdhgjfhgj
yyyy-land-tm
ABC
sjg

Which function can help achieve the above?

Comments

Frank Kulash

Hi, User_97XVQ
If you want the sub-string of str from the start of the string up to (but not including) the last substring of hyphen-digits-hyphen, then you can use:

REGEXP_SUBSTR ( str
	      , '(.*)-\d+-'
	      , 1
	      , 1
	      , NULL
	      , 1
	      )

If you'd care to post CREATE TABLE and INSERT statements for the sample data, then I could test it.
If str does not contain a sub-0string of all digits surrounded by hyphens, then the expression above returns NULL.

Frank Kulash

Hi, User_97XVQ
I see that you changed your original message after I replied. Please don't do that: it makes the thread hard to read and your changes easy to miss. Make any corrections and additions in a new reply, at the end of the thread.
If tst is a string containing one or more digits, immediately preceded and followed by a hyphen, then

REGEXP_SUBSTR ( tst
	      , '(.*?)-\d+-'
	      , 1
	      , 1
	      , NULL
	      , 1
	      )

returns the sub-string before the first occurrence of the hyphen-digits-hyphen pattern. The only change for your new requirements is the '?' in the second argument, making '.*' non-greedy (that is, matching as little as possible when there is a choice).
As before, the expression returns NULL if tst does not contain a hyphen-digits-hyphen pattern.

1 - 2

Post Details

Added on Nov 22 2021
2 comments
236 views
1 mention