Split Varchar Field to a multiple rows Oracle

Question

In Oracle i have a table that one of its fields I need to divide it into several rows. The problem is that there is no exact separator, I only know what is the value format of the attributes.

NumberVarchar united

The example would be the following.

objectid    linkvalue
    1       1V 2E 3T/B
    2       3C+1E. 3V
    3       5V.4PH
    4       V H
    5       V H 8V

And I need the output to be like the following to insert it in another table:

objectid    linkvalue
    1          1V
    1          2E
    1          3T/B
    2          3C
    2          1E
    2          3V
    3          5V
    3          4PH
    4          V H
    5          V H
    5          8V

Any idea or suggestion how to do it? Thank you very much in advance

What is the rule to split `5V4PH` to `5V` and `4PH` ? Same thing for `3C+1E. 3V` please? — Kobi, Jun 27 '18 at 15:26
@Kobi The value is given by a number concatenated by a varchar. Always the beginning of a value is numeric. The separators can be spaces, + or. In the case of example 3T/B, the number 3 and T/B are taken. When there is a . or + must be omitted — alejomarchan, Jun 27 '18 at 15:33
Possible duplicate of [Split column to multiple rows](https://stackoverflow.com/questions/3713107/split-column-to-multiple-rows) — William Robertson, Jun 27 '18 at 16:30

Gary_W · Accepted Answer · 2018-06-27T21:42:34.843

Here's another way:

with tbl(objectid, linkvalue) as (
  select 1, '1V 2E 3T/B' from dual union all
  select 2, '3C+1E. 3V' from dual union all
  select 3, '5V.4PH' from dual
)
select objectid,
       regexp_substr(linkvalue, '(.*?)([ +.]+|$)', 1, level, NULL, 1)
from tbl
connect by level <= regexp_count(linkvalue, '[ +.]+') + 1
and prior objectid = objectid
and prior sys_guid() is not null;


OBJECTID    REGEXP_SUBSTR(LINKVALUE,'(.*?)([ +.]+|$)',1,LEVEL,NULL,1)
--------    ----------------------------------------------------------
1           1V
1           2E
1           3T/B
2           3C
2           1E
2           3V
3           5V
3           4PH

Edit: Added a case for when there is no delimiter. Make a pass to add a space between a capital letter and a number. Sure it's somewhat quick and dirty, but I won't tell if you won't.

Edit2: Allowed for a value consisting of multiple single capital letters separated by 1 or more delimiters. The regex is getting ugly though.

-- Set up data set
with tbl(objectid, linkvalue) as (
  select 1, '1V 2E 3T/B' from dual union all
  select 2, '3C+1E. 3V' from dual union all
  select 3, '5V.4PH' from dual union all
  select 4, '4H6C' from dual union all
  select 5, 'C E O 8V' from dual union all
  select 6, 'V H' from dual union all
  select 7, '9X X Y Z' from dual
),
-- Add a delimiter where missing
tbl1(objectid, linkvalue) as (
  select objectid,
         regexp_replace(linkvalue, '([A-Z])([0-9])', '\1 \2')
  from tbl
)
select objectid,
       regexp_substr(linkvalue, '(([A-Z][ +.]?)+|.*?)([ +.]+|$)', 1, level, NULL, 1)
from tbl1
connect by regexp_substr(linkvalue, '(([A-Z][ +.]?)+|.*?)([ +.]+|$)', 1, level) is not null
and prior objectid = objectid
and prior sys_guid() is not null;

How can I separate the field when it comes as follows 4H6C? That is, without spaces? — alejomarchan, Jun 27 '18 at 16:13
Greetings. Would you know how I can leave the CEO or C E O value in the same field? At the time that case, I know how 3 different — alejomarchan, Jun 27 '18 at 19:25
I'm afraid I don't understand. Please update your original post and include ALL possible data issues you can. — Gary_W, Jun 27 '18 at 20:43
See edit2. Next time make sure ALL of your test cases are present in the original post! — Gary_W, Jun 27 '18 at 21:43

Kobi · Answer 2 · 2018-06-27T15:39:00.873

1

If the separators can be . or + or , this query works :

select distinct objectid,regexp_substr(linkvalue,'[^+|.| ]+', 1, level) txt
from (          select 1   objectid,  '1V 2E 3T/B'   linkvalue from dual 
       union all select 2 ,            '3C+1E. 3V'              from dual
       union all select 3,             '5V.4PH'                 from dual)
connect by regexp_substr(linkvalue, '[^+|.| ]+', 1, level) is not null
order by 1

edited Jun 27 '18 at 15:39

answered Jun 27 '18 at 15:33

Kobi

2,494
15
30

Thank you very much! It works perfect, where the field does not separate me is when the value is found as follows 1V4C, that is, when they are joined. He keeps bringing me together. The result should be 1V 4C. Will you know what I can modify? – alejomarchan Jun 27 '18 at 15:50
you should add this case in the sample data of the question. – Kobi Jun 27 '18 at 15:57
You're right, @Kobi, excuse the omission. I tried to obtain the possible values and that case I did not observe it. – alejomarchan Jun 27 '18 at 16:04

score -3 · Answer 3 · answered Jun 27 '18 at 15:45

-3

replace any break characters with a normal one (in the case below I used a spaces). Then do a cross join and filter out any blank values.

declare @data table(id int, codes varchar(50))

insert into @data values
(1, '1V 2E 3T/B'),
(2, '3C+1E. 3V'),
(3, '5V4PH')

select id, value
from (select id, replace(replace(replace(codes, 'V', 'V '), '+', ' '), '.', ' ') [codes] 
      from @data) d
cross apply string_split(d.codes, ' ')
where value <> ''

answered Jun 27 '18 at 15:45

Monofuse

735
6
14

1

you asked the version of Oracle database used, but the query you provided is for SQL Server – Kobi Jun 27 '18 at 15:51

Split Varchar Field to a multiple rows Oracle

3 Answers3