how to get specific values from a string

curious_mind

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.

JonWat

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 '_'

Frank Kulash

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

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

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}?)