1

I would like to extract following string in Oracle. How can I do that?

  • Original String: 011113584378(+) CARD, STAFF
  • Expected String: STAFF CARD
Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
Hakan
  • 141
  • 3
  • 16
  • 3
    What is the logic to go from the input to the output? Will the format always be SPACE COMMA SPACE ? Are the sequences of letters always non-empty? Are they only letters, or can they also contain DASH ( - ) or UNDERSCORE ( _ ) or digits? Please clarify the logic before we think of any kind of code. –  Jun 16 '17 at 22:13

2 Answers2

0

I presume you have the luxury of writing a PL/SQL function? Then just use "SUBSTR", and/or "INSTR", and || concatenation operator to parse your input.

Here is an example:

https://www.techonthenet.com/oracle/questions/parse.php

...The field may contain the following value:

F:\Siebfile\YD\S_SR_ATT_1-60SS_1-AM3L.SAF

In this case, I need to return the value of '1-60SS', as this is the value that resides between the 3rd and 4th underscores.

SOLUTION:

create or replace function parse_value (pValue varchar2)
   return varchar2
is
   v_pos3 number;
   v_pos4 number;

begin

   /* Return 3rd occurrence of '_' */
   v_pos3 := INSTR (pValue, '_', 1, 3) + 1;

   /* Return 4rd occurrence of '_' */
   v_pos4 := INSTR (pValue, '_', 1, 4);

   return SUBSTR (pValue, v_pos3, v_pos4 - v_pos3);

end parse_value;
paulsm4
  • 114,292
  • 17
  • 138
  • 190
  • 4
    Why would anyone need to write a PL/SQL function to use SUBSTR and INSTR? –  Jun 16 '17 at 22:11
0

Ok, I'll bite. This example uses REGEXP_REPLACE to describe the string, saving the parts you need in order to rearrange them before returning them. It would be better if you showed some real-world examples of the data you are dealing with as I can only guarantee this example will work with the one line you provided.

The regular expression matches any characters starting at the beginning of the string and ending with a close paren-space. The next set of any characters up to but not including the comma-space is "remembered" by enclosing them in parens. This is called a captured group. The next captured group is the set of characters after that comma-space separator until the end of the line (the dollar sign). The captured groups are referred to by their order from left to right. The 3rd argument is the string to return, which is the 2nd and 1st captured groups, in that order, separated by a space.

SQL> with tbl(str) as (
     select '+011113584378(+) CARD, STAFF' from dual
   )
   select regexp_replace(str, '^.*\) (.*), (.*)$', '\2 \1') formatted
   from tbl;

FORMATTED
----------
STAFF CARD

SQL>
Gary_W
  • 9,933
  • 1
  • 22
  • 40