Oracle SQL Split by Delimiters Without Store Procedure/Function

Question

I want to split a column by delimiters (|) in oracle db. How can i do that?

In db the value will be stored as:

Field

AP1|Apple Juice|100

I want to separate it become:

Field1 | Field2 |Field3

AP1 Apple Juice 100

I dont know the exact query for that but basically below is what i want.

select split(Field) as Field1, split(Field) as Field2, split(Field) as Field3

I wanted to use just SQL (without creating any store procedure or function).

There are various different solutions in the post I've identified. Choose the one which fits your version and other needs. Probably the SQL with regex solution is the easiest to implement. (All the solutions use `,` as the delimiter so replace that with `|`). — APC, Nov 12 '15 at 08:14
Sorry forgot to mention, i wanted to use just query (without any creation of store procedure, functions), but the given examples used it. — hades, Nov 12 '15 at 08:16

score 5 · Accepted Answer · edited Nov 12 '15 at 09:24

5

With regex :

with sample_data as
    (select 'AP1|Apple Juice|100' str
    from dual
)
select str,
     regexp_substr(str, '[^|]+', 1, 1) str1,
     regexp_substr(str, '[^|]+', 1, 2) str2,
     regexp_substr(str, '[^|]+', 1, 3) str3
from sample_data;

edited Nov 12 '15 at 09:24

Florin Ghita

17,525
6
57
76

answered Nov 12 '15 at 08:43

Renaud Kern

1,116
10
25

score 1 · Answer 2 · edited May 23 '17 at 11:51

You could do it in pure SQL using SUBSTR and INSTR to extract the substrings between the delimiter |.

For simple string manipulation, I prefer SUBSTR + INSTR approach as it is still faster than the REGEXP. Of course with recent releases, the performance difference is decreasing, however, REGEXP is still more CPU intensive than the old SUBTR+INSTR.

For example,

SQL> WITH sample_data AS
  2    (SELECT 'AP1|Apple Juice|100' str
  3    FROM dual
  4    )
  5  -- end of sample_data mocking as a table
  6  SELECT str,
  7         SUBSTR(str, 1, instr(str, '|', 1,1) -1) str1,
  8         SUBSTR(str, instr(str, '|', 1, 1) + 1,
  9                   instr(str, '|', 1, 2) -
 10                  instr(str, '|', 1, 1) -1) str2,
 11         SUBSTR(str, instr(str, '|', 1, 2) + 1) str3
 12  FROM sample_data;

STR                 STR1                STR2                STR3
------------------- ------------------- ------------------- ----
AP1|Apple Juice|100 AP1                 Apple Juice         100

On a side note, you should not store data as delimited string, it is really a bad design. You should normalize it as a permanent fix. As far as possible, store the attributes in different columns, then you do not have the overhead of manipulating the string to extract respective attributes.

Using the plain good old `instr()`+`substr()` combo in these days full of regexp's. I like this answer. — peter.hrasko.sk, Nov 12 '15 at 08:57
thanks for the answer, yeah realized that it is not a good design. — hades, Nov 12 '15 at 13:37
@user3172596 Also, understand when and where to use REGEXP as it is not going to be better in terms of performance on a large data set with such denormalized data. — Lalit Kumar B, Nov 12 '15 at 13:39

Oracle SQL Split by Delimiters Without Store Procedure/Function

2 Answers2