Extract String

User_97XVQ

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?

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.