how to get specific values from a string — oracle-tech

    Forum Stats

  • 3,714,819 Users
  • 2,242,634 Discussions
  • 7,845,078 Comments

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

how to get specific values from a string

curious_mind
curious_mind Member Posts: 249 Bronze Badge
edited November 2020 in SQL & PL/SQL

Hi All,


I have a requirement where i will be having a particular string like as mentioned below:

205_CL_GRP_DTL

now i have to extract the value by ignoring number from the front and the first _

I have used below sql statement:

select regexp_replace('205_CL_GRP_DTL', '[0-9]', '') from dual;

but it is giving me values like _CL_GRP_DTL

but my requirement is to get string value as CL_GRP_DTL

Could someone please suggest on the same.

Answers

  • JonWat
    JonWat Member Posts: 519 Silver Badge

    with inp as

     (select '205_CL_GRP_DTL' pstring from dual)

    select substr(inp.pstring,instr(inp.pstring,'_')+1)

    from inp

    i.e take everyting in the string after the first '_'

    curious_mind
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 39,951 Red Diamond

    Hi,

    now i have to extract the value by ignoring number from the front and the first _

    I have used below sql statement:

    select regexp_replace('205_CL_GRP_DTL', '[0-9]', '') from dual;

    That doesn't just remove digits from the front (that is, the beginning) of the string; it will remove all digits, no matter where they occur. If you want to remove digits only from the beginning of the string, then anchor the expression to the beginning or the string, using ^, like this:

    REGEXP_REPLACE (str, '^[0-9]+_?')
    

    The pattern this looks for is

    • At the beginning of the string ('^')
    • any digits ('[0-9]): one or more of them ('+')
    • an underscore character ('_'), if that's the next character ('?')

    What would you want if the string started with digits but no underscore, e.g. '2FOO_34_BAR'?

    Instead of '[0-9]' you could also use '\d', like this

    REGEXP_REPLACE (str, '^\d+_')
    


  • Paulzip
    Paulzip Member Posts: 8,076 Gold Crown
    edited November 2020

    If you want to use regex, you'd probably be better off with regexp_substr :

    select regexp_substr('205_CL_GRP_DTL', '^(\d*_?)(.*)$', 1, 1, 'i', 2)
    from dual
    

    Note, I've made some assumptions here though (including that you aren't on anything older than 11g), that your string MIGHT start with numbers and an underscore.


    Regex explained :

    ^(\d*_?)(.*)$

    Assert position at the beginning of the string «^»

    Match the regex below and capture its match into backreference number 1 «(\d*_?)»

      Match a single character that is a “digit” (any decimal number in any Unicode script) «\d*»

       Between zero and unlimited times, as many times as possible, giving back as needed (greedy) «*»

      Match the character “_” literally «_?»

       Between zero and one times, as many times as possible, giving back as needed (greedy) «?»

    Match the regex below and capture its match into backreference number 2 «(.*)»

      Match any single character that is NOT a line break character (line feed) «.*»

       Between zero and unlimited times, as many times as possible, giving back as needed (greedy) «*»

    Assert position at the end of the string, or before the line break at the end of the string, if any (line feed) «$»

  • mathguy
    mathguy Member Posts: 9,463 Gold Crown

    There are really (at least) two questions you need to answer, to clarify your question. For both, you may answer "this cannot happen in my data". But if it can, you must explain the desired handling.

    First, as Mr. Kulash explained, what if you have digits at the beginning, but not followed immediately by an underscore?

    Second, what if you do have underscores, but the "first token" is not all digits? Like ABC_BLAH_BLAH_BLAH - do you still want the first "token" removed, or should this string be returned as is?

    (OR are ALL your strings in the format {one or more digits}_{something else}?)

Sign In or Register to comment.