Now there is a table
Equipment of equipment name IP results
Beijing belong center office ME60 () is a field 222.83.19.109 CR56LPUF400A, 210305498910 j7000268, 03054989; CR56LPUF100B0, 210305377710 h3000190, 03053777; G1000861 CR56LPUF100B0, 210305377710, 03053777 (this is a field)
Shanghai 80 () is a field 222.83.19.158 CR52EAGF0, 0386751073000124, the PIC. CR52EAGF, 0278671073000167, LPU; CR52EAGF0, 0386751068000350, the PIC. CR52EAGF, 0278671067000055, LPU; CR52SRUA0, 0391091073000356, MPU; CR52SRUA0, 0391091073000342, SFU
. . . .
Which results in this field is very long, I just capture some of the paste, now to the results of this field to break up, for the first time into:
CR56LPUF400A, 210305498910 j7000268, 03054989
CR56LPUF100B0, 210305377710 h3000190, 03053777
CR56LPUF100B0, 210305377710 g1000861, 03053777
The second split
CR56LPUF400A 210305498910 j7000268 03054989
CR56LPUF100B0 210305377710 h3000190 03053777
CR56LPUF100B0 210305377710 g1000861 03053777
Then split fields from a list of other tables, the following
Equipment apanage device name IP type single board coding veneer description
Beijing (who center office ME60) a field 222.83.19.109 CR56LPUF400A 210305498910 j7000268 03054989
Beijing belong center office ME60 222.83.19.109 CR56LPUF100B0 210305377710 h3000190 03053777
Beijing belong center office ME60 222.83.19.109 CR56LPUF100B0 210305377710 g1000861 03053777
Shanghai huawei NE80E (80) 222.83.19.158 CR52EAGF0 0386751073000124 PIC
Shanghai 80 innings huawei NE80E 222.83.19.158... . .
. . . . . .
Now need to use oracle stored procedures to implement, I beginner, don't know how to do it, the trouble which give advice, online, etc., thank you

CodePudding user response:

In fact is the semicolon split time, according to the comma split again
Break up can use connect by cooperate REGEXP_SUBSTR, very troublesome
Seems there are some other exotic curiosity-a solution looking
This kind of thing I suggest don't put in storage process, which is difficult to write and maintain
With an external script, difficulty is reduced a lot, maintenance is easy

CodePudding user response:

You can use the instr function + substr function to help you solve this problem

CodePudding user response:

refer to the second floor weixin_41637486 response:

instr function + substr function can you use to help you solve this problem

with split function first, according to "; 'cut the data into multiple lines, and then a field according to their instr + substr split into multiple fields

CodePudding user response:

The SELECT regexp_substr (' CR56LPUF400A, 210305498910 j7000268, 03054989; CR56LPUF100B0, 210305377710 h3000190, 03053777; G1000861 CR56LPUF100B0, 210305377710, 03053777 ', '+' [^;,], 1, LEVEL)
The FROM dual
CONNECT BY regexp_substr (' CR56LPUF400A, 210305498910 j7000268, 03054989; CR56LPUF100B0, 210305377710 h3000190, 03053777; G1000861 CR56LPUF100B0, 210305377710, 03053777 ', '+' [^;,], 1, LEVEL) IS NOT NULL

CodePudding user response:

Write a spilt function, reference
The create or replace function split (p_str varchar2, p_delimiter varchar2 default ', ') return type_str

Is

The rs type_str: type_str=();

L_str varchar2 (4000) :=';

L_len number:=0;

The begin

L_str:=p_str;

L_len:=length (p_delimiter);

While length (l_str) & gt; 0 loop

If instr (l_str p_delimiter) & gt; 0 then

Rs. The extend;

Rs (rs. Count) :=substr (l_str, 1, instr (l_str p_delimiter) - 1);

L_str=substr (l_str, instr (l_str p_delimiter) + l_len);

The else

Rs. The extend;

Rs (rs. Count) :=l_str;

exit;

end if;

End loop;

return rs;

end;

CodePudding user response:

The SELECT regexp_substr (' CR56LPUF400A, 210305498910 j7000268, 03054989; CR56LPUF100B0, 210305377710 h3000190, 03053777; G1000861 CR56LPUF100B0, 210305377710, 03053777 ', '[^ (; |,)] +', 1, LEVEL)
The FROM dual
CONNECT BY level<=regexp_count (column, '(; |)] ') + 1
Here (; |,) I don't know whether it is feasible to, the original poster can replace with two regular