Forum Stats

  • 3,781,549 Users
  • 2,254,529 Discussions
  • 7,879,752 Comments

Discussions

Extract String

User_97XVQ
User_97XVQ Member Posts: 83 Red Ribbon
edited Nov 22, 2021 8:48PM in SQL & PL/SQL

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?

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,384 Red Diamond
    edited Nov 23, 2021 1:25AM

    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
    Frank Kulash Member, Moderator Posts: 41,384 Red Diamond

    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.